Need some VBA advice

[Valandil]

I need someone who is fairly proficient with VBA to give me some advice on tweaking a macro I am working on for Excel 2007. What I need to is be able to change the Autofill Destination:=Range from ("A2:A6536") to "A2:variable number of rows"). Basically, the reports I'm running this macro on have a different number of rows each time the data is generated. I tried using ("A2:A" and lstRow) but to no avail. Entire code is as follows:

Sub VENamesFormulaMacro()
'
' FormulaMacro Macro
'

    ' Add the VE analyst names
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "VETeam"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[5],VETeam!C[1]:C[2],2,FALSE)"
    Selection.AutoFill Destination:=Range("A2:A65536")
    Range("A2:A65536").Select
    
    ' Autosize the column after filling it all in.
    Columns("A:A").Select
    Columns("A:A").EntireColumn.AutoFit
    
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Comments"
    
    ' Autosize the column after filling it all in.
    Columns("A:A").Select
    Columns("A:A").EntireColumn.AutoFit
     
    
End Sub

 

[Miros]

I wish I could help but I know nothing about VBA. D: