Posts by aisietie

Hello,

I want to paste a row in the first free row in a range. The first row can be empty.
I try the code

Destination:=Worksheets(strHelp).Range("A101:A200").End(xlDown).Offset(1, 0)

But this is not working.

Can somebody help?

aisietie

Re: Insert Row, copy formula from above

PCI,

OK, this works, and solves my problem.

Thanks, I just wonder if this can be done with a 1-liner

aisietie

Hello,

I need to insert a row every time at row 98
But when I do this the formule from above is not copied.

I try it with this code:

Sheets(strSheetName).Rows("98:98").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

But a row is inserted, without the formule from row 97

Can somebody help?

aisietie

Re: Scroll to column that contains date today

Thanks Smallman.

aisietie

Hello,

I placed a question, but got no answer, so I think it is better to split the problems.
With the code

Sub GaNaarKolomVandaag()
Dim c As Range
For Each c In Range("O1:NS1")
If c = Now() Then
ActiveWindow.ScrollColumn = c.Column
Exit Sub
End If
Next c
End Sub

I try to go to the column with date today in the first row.
I prefer that the column with date today should be the first row next to the blocked title column.

But the macro does not work.

Can somebody help?

Thanks

aisietie

Re: Making Command Button invisible from within the click-event

DJ,

Just use

ShowAsGraph_Click()
ActiveSheet.ChartObjects("Dashboard_Chart").Visible = true
Showasgraph.Visible = false


End sub

aisietie

Re: Delete row + add blank row in range

Hello,

Yes, I use it somewhere else.

The problem is the same when I add or paste a row in 1 of the ranges.

aiietie

Hello,

I have a sheet with 3 datablocs rows 1 - 9, rows 11 - 19, rows 20 - 29. Not all rows have data, but the data is always in the first rows of the ranges. The lastrows can be empty.
I would like to keep these ranges when a row is cut or added
Now when I cut 2 rows with a macro, lets say, row 3 and 4, the data that started in row 11, is now in row 9. This is not good, it should stayed in row 11
So I wonder if it is possible, that when I cut rows, the same number empty rows is added starting from the last row in the range?
The same when I add a new row with data in a range, when this row is added in the first free row of a range, the ranges total must remain 30 rows.
Because the cut or add is done with VBA, I would like it that this was possible with VBA.
Thanks

aisietie

Re: paste in different range on condition

Thank You.
This is what I need.

Great Job

Thanks

aisietie

Hello,
In the file in the attachement, names are copied to different sheets on a condition that is in column 2.
(Macro Searchonstring)
Example all rows with FS in column 2 are copied to sheet FS, with CF in column 2 are copied to Sheet CF,...
But I have a 3th column that can contain 2 values: OK or NOK
What I want is that rows with FS in column 2 are copied to sheet FS, this works already, but then those with OK in column 3 must be pasted in the Range A1:A10, those with NOK must be pasted in the range A11:A20
Thanks for helping
aisietie

Re: Workbook by default open in Read-Only mode with Commandbutton to normal mode

Jindon,

That is a great help.
I will try this in my project.
Thanks,

aisietie

Re: Workbook by default open in Read-Only mode with Commandbutton to normal mode

Hello,

Can I understand here, that what I want is impossible?

Grtz

aisietie

Hello,

Because several users have to read an excelfile(that is placed on a shared folder), it would be interessant to, by default open the workbook in the Read-Only Mode, and by clicking a commandbutton, place the file in normal mode.
In that way, while the file is read by different users, another user still can change the file.
Now it is mostly the case, once the file is opened, an other user can not change the file, and yet, the file is opened by users who are only consulting.

So, is it possible to always let the workbook open in Read-Only mode, and with a commandbutton put the workbook in Normal mode?

Thanks in advance,

aisietie

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

Stephen,

That is a big OK.

Thanks

aisietie

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

At this moment I have a button that cut a row and paste the row in an other sheet.

Sheets("Input").Range("A" & ComboBox1.ListIndex + 3 & ":ZZ" & ComboBox1.ListIndex + 3).Cut Destination:=Worksheets(strWs).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

I would like, that after the cut, the empty row is deleted.
So i thought by checking if there is an empty cell in column A, then delete this row.

aisietie

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

OK, how can I let this or another function do the thing?

aisietie

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

Thx for the reply.

But is it possible to let it work with a commandbutton click?

Can this function be implemented in the commandbutton click event?

aisietie

Hello,

I found a function that founds the last used cell in a column.

Public Function GetLastCell(ByVal LastCell As Range) As Long
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long


Set WS = Worksheets("Sheet1")
With WS
    Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
    LastCellRowNumber = LastCell.Row
End With
End Function

Now I need to delete all the rows above that row with a blanc cell in that column.

So the macro must find the last cell in a column, and then delete all the rows above that have a blanc cell in this column.

Can somebody help?

Grtz

aisietie

Re: find row in datatable when click on cell in pivot table

All,

It is OK.
What do I do:

In the pivottable I double click in the cell wich I want to see the info. A new page is generated, and then I run a macro wich sets advanced filter in the data table.
Then I get the corresponding row in the data table.

Thx anyway.

aisietie

Re: find row in datatable when click on cell in pivot table

All,

Is what I want impossible?

Grtz

aisietie