I went with the following, which seems to work, although I was
intrigued with Gord's suggestion. In the end I couldn't easily follow
the various steps, so I took the easy way out. I wonder though if
Gord's method would work even if a blank cell occurred in the middle
of the column being used to get the last row...
Thanks to all,
Kebbon

On Aug 16, 11:12 am, Gord Dibben <gorddibbATshawDOTca wrote:
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- Hide quoted text -

- Show quoted text -