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

Use xlDown on one of the columns imported and allocate the row number
of the ActiveCell to a variable which is then used in your paste
operation.

Hope this helps.

Pete

On Aug 16, 3:33 am, "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

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

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 -

I am confused........you say you "went with the following but were intrigued by
Gord's suggestion" but I see my code is posted in your message.

Which "following" did you go with?

To answer the question about blank cells.........Yes, Gord's method starts its
search from the bottom of column D and works back up to last filled cell.

Ignores blanks in the middle of the column.

A straight End(xlDown) stops at the first blank cell in the column.


Gord


On Thu, 16 Aug 2007 20:57:36 -0000, " wrote:

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 -