How to select the last range of cells in 1 range of columns and fill down to the last row in another range of columns

1
votes

I am attempting to copy formulas from the last row in columns "O" to "V" down to the last used row in column "A" but I am having difficulty. This is a bit more advanced than I am used to as I can manage to get data from cell "O2" to copy down to the last row but the formula will always been in a different row and it is a large set of data I am adding to and therefore I don't wish to recalculate the full page each day.

Extract of Code

 LastRow = ActiveSheet.UsedRange.Rows.Count
LR1 = Range("O" & Rows.Count).End(xlUp).Row
Range("O" & LR1).Select
Selection.Copy
LastRow = ActiveSheet.UsedRange.Rows.Count
LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("O" & LR1).AutoFill Destination:=Range("O" & LR2), Type:=xlFillDefault

It looks to be the last line that is causing the issues but it could be incorrect. To give you a bit more of what I have completed to this point- I have copied 4385 lines of data into columns "A:N" and now need to copy the formula that was in the 1st row I pasted into down to the end of my data set now + 1 extra row.

I am then wanting to select the formulas that I have copied down and paste special all but the last row of formula in columns "O to V"

Is it possible to do this?

1
vbaexcel

1 Answers

1
votes

You should be able to avoid actually selecting anything with this.

Dim LR1 As Long, LR2 As Long
LR1 = Range("O" & Rows.Count).End(xlUp).Row
LR2 = Range("A" & Rows.Count).End(xlUp).Row
Range("O" & LR1 & ":V" & LR2).FillDown

*Addendum: * The correct command was FillDown, not Autofill.