I'm trying to paste a formula with a dynamic range, it could be 2000 rows to
10000 rows (see below). I tried defining LastRow using a Function but it
won't work. Anyone have a better way? The brute force alternative is to set
a large range, which works, but is messy because I want to then sort in a way
that leaves me with up to thousands of empty cells in the wrong place.

ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveCell.Range("A1:A(LastRow)").Select
ActiveSheet.Paste


Thanks for any ideas.

Alan

try

range("a1:a"&cells(rows.count,"a").end(xlup).row). copy


--
Don Guillett
SalesAid Software

"Alan P" wrote in message
...
I'm trying to paste a formula with a dynamic range, it could be 2000 rows
to
10000 rows (see below). I tried defining LastRow using a Function but it
won't work. Anyone have a better way? The brute force alternative is to
set
a large range, which works, but is messy because I want to then sort in a
way
that leaves me with up to thousands of empty cells in the wrong place.

ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveCell.Range("A1:A(LastRow)").Select
ActiveSheet.Paste


Thanks for any ideas.

Alan

ActiveCell.Range("A1:A(LastRow)").Select

would raise an error. You would use

ActiveCell.Range("A1:A" & LastRow).Select

to find the last cell with date

lastrow = Cells(rows.count,2).End(xlup).Row

change 2 to match the column that will determine the extent of the data.

You can then just do
lastrow = Cells(rows.count,2).End(xlup).Row
Range("A1:A" & LastRow)..formula = range("A1").Formula


However, your recorded code is relative to the selection, so I don't know
where you actually are at that time. So the above might need to be modified.

--
Regards,
Tom Ogilvy


"Alan P" wrote in message
...
I'm trying to paste a formula with a dynamic range, it could be 2000 rows
to
10000 rows (see below). I tried defining LastRow using a Function but it
won't work. Anyone have a better way? The brute force alternative is to
set
a large range, which works, but is messy because I want to then sort in a
way
that leaves me with up to thousands of empty cells in the wrong place.

ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveCell.Range("A1:A(LastRow)").Select
ActiveSheet.Paste


Thanks for any ideas.

Alan

Tom,

Thanks for your help, it was exactly what I needed. Works great.

Alan

"Tom Ogilvy" wrote:

ActiveCell.Range("A1:A(LastRow)").Select

would raise an error. You would use

ActiveCell.Range("A1:A" & LastRow).Select

to find the last cell with date

lastrow = Cells(rows.count,2).End(xlup).Row

change 2 to match the column that will determine the extent of the data.

You can then just do
lastrow = Cells(rows.count,2).End(xlup).Row
Range("A1:A" & LastRow)..formula = range("A1").Formula


However, your recorded code is relative to the selection, so I don't know
where you actually are at that time. So the above might need to be modified.

--
Regards,
Tom Ogilvy


"Alan P" wrote in message
...
I'm trying to paste a formula with a dynamic range, it could be 2000 rows
to
10000 rows (see below). I tried defining LastRow using a Function but it
won't work. Anyone have a better way? The brute force alternative is to
set
a large range, which works, but is messy because I want to then sort in a
way
that leaves me with up to thousands of empty cells in the wrong place.

ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveCell.Range("A1:A(LastRow)").Select
ActiveSheet.Paste


Thanks for any ideas.

Alan