VBA Autofill Range Adjustment

Question

I have recorded a macro in Excel, but when I run it with updated data, it only applies to the number of rows I had when I first had the macro recorded. I dove into the macro code and I think these are the problems:

Selection.AutoFill Destination:=Range("L2:R2"), Type:=xlFillDefault
    Range("L2:R2").Select
    Selection.AutoFill Destination:=Range("L2:R242")
    Range("L2:R242").Select

How can I adjust the macro to have the range set up as the size of the worksheet?

You could [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Mar 08 '21 at 14:35

score 0 · Answer 1 · answered Mar 08 '21 at 14:38

0

Save the last row of your data into a variable and then modifiy the line to always fill the formula to that value like this:

Dim Last_Row as Long
Last_Row = Application.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("L2:R2").AutoFill Destination:=Range("L2:R" & Last_Row), Type:=xlFillDefault

(Rows.Count, 1) this 1 represents the Column A. I supposed your data is here

answered Mar 08 '21 at 14:38

David Donayo

117
9

1

When I make the changes, it only applies to a single column on Column E. I have columns from E to K that need this to be applied to. – byrak79 Mar 08 '21 at 15:11
So try using a loop and doing AutoFill on every single Column You need – David Donayo Mar 09 '21 at 13:16

VBA Autofill Range Adjustment

1 Answers1