VBA Code for Selecting a Cell Formula and Autofilling Down

Question

I'm trying to write a VBA code that will autofill the formula in cell H2 down through column H. I have code from recording a macro, but I need it to work on a regular report where the number of rows will change over time. Essentially, I'll want to autofill H2 down as far as there are values in column A, but this one will autofill through row 30533 every time regardless of how many rows of data there actually are, right?

Sub Select_Cell_Copy_Down()


Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H30533")

Range("H2:H30533").Select

End Sub

Answer 1

You can do it like this:

Sub Select_Cell_Copy_Down()
    Dim ws As Worksheet, rng As Range
    
    Set ws = ActiveSheet
    'use the last-occupied cell in Col A to set the range in Col H
    Set rng = ws.Range("H2:H" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
    
    ws.Range("H2").AutoFill Destination:=rng
    rng.Select
End Sub

Note you rarely need to Select / Activate anything in VBA - that's only an artifact of the way the macro recorder tracks your actions.