VBA Code - Autofill Down - Copying two cells down to the end of the last row

New Contributor

Need help correcting a Macro that will automatically copy data in two cells down to the last row.  I have attempted to use the other conversations similar to this to fix error I am receiving but I continue to get errors.  Here's the code I have now:


Sub CopyDownAA_AB() ' ' CopyDownAA_AB Macro ' '


     Selection.AutoFill Destination:=Range("AA2:AB105")



End Sub








4 Replies


The code looks OK (although it is not necessary to select ranges). What is the error message that you receive?

@Hans Vogelaar Thanks for getting back with me!  Each sheet has a different number of rows.  So when I use the macro on another sheet, it only goes down to Row 105, even if I have 200 rows.  I need it to copy down to the last row, regardless of how many rows there are.

best response confirmed by JT_FrankGroup (New Contributor)


Try this version:

Sub CopyDownAA_AB()
    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("AA2:AB2").AutoFill Destination:=Range("AA2:AB" & LastRow)
End Sub