Autofill in VBA with a dynamic "Destination" range

I'm trying to set up this macro to autofill in VBA:

Sub filldateinfo()
 
'   Format the new cells
    Range("D2").Select
    Selection.Copy
    Range("F2:G2").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
'   Add the formulas to get the year and month
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(MID(RC4,3,1)=""."",MID(RC4,3,1)=""-"",MID(RC4,3,1)=""/""),RIGHT(RC4,4),IF(OR(MID(RC4,5,1)=""."",MID(RC4,5,1)=""-"",MID(RC4,5,1)=""/""),LEFT(RC4,4),""""))"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(MID(RC4,3,1)=""."",MID(RC4,3,1)=""-"",MID(RC4,3,1)=""/""),LEFT(RC4,2),IF(OR(MID(RC4,5,1)=""."",MID(RC4,5,1)=""-"",MID(RC4,5,1)=""/""),RIGHT(RC4,2),""""))"
'   Autofill to the end of the table
   Range("F2:G2").Select
   Selection.AutoFill Destination:=Range("F2:G8489")  'How do I make this dynamic based on the length of column A?
   Range("F2:G8489").Select  'How do I make this dynamic based on the length of column A?
'   Replace the formulas with values
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'   Re-arrange the table
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Cut Destination:=Columns("H:H")
    Columns("F:H").Select
    Selection.Cut Destination:=Columns("D:F")
    Range("A1").Select
 
End Sub

The length of the table will vary, so I need the autofill to determine it's own "Destination" value based on the length of column A.

See attached for sample.

Cheers,

Try this:

Sub dateinfo()

'   Format the new cells
    Range("D2").Copy
    With Range("F2:G2")
        .PasteSpecial Paste:=xlPasteFormats, _
                      Operation:=xlNone, _
                      SkipBlanks:=False, _
                      Transpose:=False
    End With
    Application.CutCopyMode = False
'   Add the formulas to get the year and month
    Range("F2").FormulaR1C1 = _
        "=IF(OR(MID(RC4,3,1)=""."",MID(RC4,3,1)=""-"",MID(RC4,3,1)=""/""),RIGHT(RC4,4),IF(OR(MID(RC4,5,1)=""."",MID(RC4,5,1)=""-"",MID(RC4,5,1)=""/""),LEFT(RC4,4),""""))"
    Range("G2").FormulaR1C1 = _
        "=IF(OR(MID(RC4,3,1)=""."",MID(RC4,3,1)=""-"",MID(RC4,3,1)=""/""),LEFT(RC4,2),IF(OR(MID(RC4,5,1)=""."",MID(RC4,5,1)=""-"",MID(RC4,5,1)=""/""),RIGHT(RC4,2),""""))"
'   Autofill to the end of the table
    Range("F2:G2").AutoFill Destination:=Range("F2:G" & Range("A" & Rows.Count).End(xlUp).Row) 'How do I make this dynamic based on the length of column A?
'   Replace the formulas with values
    With Range("F2:G" & Range("A" & Rows.Count).End(xlUp).Row)
        .Copy
        .PasteSpecial Paste:=xlPasteValues, _
                      Operation:=xlNone, _
                      SkipBlanks:=False, _
                      Transpose:=False
    End With
    Application.CutCopyMode = False
'   Re-arrange the table
    Columns("E:E").Cut Destination:=Columns("H:H")
    Columns("F:H").Cut Destination:=Columns("D:F")
    Range("A1").Select

End Sub

Regards

Avoid 'select' and 'activate' in VBA.

Sub snb()
  Range("D2").Copy Range("F2:G2")
  with Range("F2:G2")
    .FormulaR1C1 ="=IF(OR(MID(RC4,3,1)=""."",MID(RC4,3,1)=""-"",MID(RC4,3,1)=""/""),RIGHT(RC4,4),IF(OR(MID(RC4,5,1)=""."",MID(RC4,5,1)=""-"",MID(RC4,5,1)=""/""),LEFT(RC4,4),""""))"
     .AutoFill .resize(cells(rows.count,1).end(xlup).row-1)
     with .resize(cells(rows.count,1).end(xlup).row-1)
       .value=.value
     end with
  end with
End Sub

Works great!

Thanks for the help in cleaning up the code too!

You're welcome. Thanks for the feedback.