Solved: Filling a formula

[Klartigue]

[VBA]Sub bloomberg()
'
' bloomberg Macro
'
'
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Select
ActiveCell.FormulaR1C1 = "=BDP(RC[-1]& "" muni"",""id_cusip"")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F1000")
Range("F2:F1000").Select
End Sub[/VBA]


As you can see, this macro fills the formula from F2 to F1000. However, I want it to fill only how many rows are in column F. There could be 2 rows or 100 rows. How to I create a general macro to fill column F?

[Bob Phillips]

I'll tell you when you add VBA tags.

[Klartigue]

[VBA]Sub bloomberg()
'
' bloomberg Macro
'
'
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Select
ActiveCell.FormulaR1C1 = "=BDP(RC[-1]& "" muni"",""id_cusip"")"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F1000")
Range("F2:F1000").Select
End Sub[/VBA]

[Bob Phillips]

Excellent, thank-you.

[vba]

Sub bloomberg()
'
' bloomberg Macro
'
'
Dim lastrow As Long

lastrow = Cells(Rows.Count, "E").End(xlUp).Row
Columns("F:F").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Resize(lastrow - 1).FormulaR1C1 = "=BDP(RC[-1]& "" muni"",""id_cusip"")"
End Sub
[/vba]

[Klartigue]

Thank you!