Assuming last row can be determined by another column.

I used Column D in this case.

Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Range("E1").Copy Destination:=Range("E3")
Lrow = Range("D" & Rows.Count).End(xlUp).Row
Range("E3:E" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 15 Aug 2007 22:33:51 -0400, "Kebbon" wrote:

I have a macro that loads a variable length data spreadsheet (n rows) into
an instance of a template. I then want to copy a formula into a blank
column of that sheet, but limit the copy only to the number of rows that
contain data.

So I have some code that selects say E1, copies its formula, goes to E3
(under the headings). Then I need to have it select from there to the last
active row and do my paste.

However, because the column is empty, it always seems to want to select all
the way to row 65536 or so. That disrupts many other things and is not at
all desirable. Can anyone tell me how to specify the row number at the end
of the imported data, so that I can limit the extent of my paste operation?
Thanks,
Kebbon