ctrl + down

[James]

I have columns A,B,C,D,E all with 66 rows of data(in other samples i have
more or less than the 66 rows, this is just given for my example). In column
F, i have my formula that I want to carry down to row 66.

But when I wite my code "selection.end(xlDown).select" it is selecting the
entire column and when it copies the formula down it goes past my row 66 in
this example and puts 0 in the cells past row 66.

How do you write the code that will select the cells in column F, but will
only select the correct amount of cells dependent on how many rows are filled
in the other columns. All other columns will have the same amount of rows.

Thanks for the help

 

[Don Guillett]

As ALWAYS, post YOUR code for comments

Sub getlastrow()
lastrow=cells.SpecialCells(xlCellTypeLastCell).row
End Sub

 

[James]

Don,
Thanks for the input it was not exactly what I was looking for but I figured
out my probelm.

Sub Macro6()

Dim endrow As Long
endrow = Range("A65536").End(xlUp).Row
Selection.AutoFill Destination:=Range("F1:F" & endrow)
End Sub

Thanks,
James

 

[Lars-Åke Aspelin]

I have columns A,B,C,D,E all with 66 rows of data(in other samples i have
more or less than the 66 rows, this is just given for my example). In column
F, i have my formula that I want to carry down to row 66.

But when I wite my code "selection.end(xlDown).select" it is selecting the
entire column and when it copies the formula down it goes past my row 66 in
this example and puts 0 in the cells past row 66.

How do you write the code that will select the cells in column F, but will
only select the correct amount of cells dependent on how many rows are filled
in the other columns. All other columns will have the same amount of rows.

Thanks for the help

Try if this helps:

If cell F1 is selected the following code will select the cells in
column F corresponding to the data in column E:

Selection.Resize(Selection.Offset(0, -1).End(xlDown).Row, 1).Select

Is that what you want? / Lars-Åke

 

[Don Guillett]

It would have been if a wasn't your longest column

 

[Gord Dibben]

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("E" & Rows.Count).End(xlUp).Row
Range("F1:F" & Lrow).FillDown
End With
End Sub

Looks at column E for last filled cell and copies your formula from F1 down that
far.


Gord Dibben MS Excel MVP