Pasting with Dynamic Last Row
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 |
Pasting with Dynamic Last Row
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 |
Pasting with Dynamic Last Row
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 |
All times are GMT +1. The time now is 07:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com