VBA code edit to populate to end of data

[bopha99]

I have the VBA code below. I would like to add 3 things to it. First, I would like to have columns I and F deleted. Next, I would like it to Auto Fill to the last row in the work sheet. I have right now:

Selection.AutoFill Destination:=Range("F2:F25399") where F25399 is the last row of the data. How do I get VBA to go to the last row of data without re entering F25399? My code is attached. Thanks for the help in advance.

Sub Holdings()
'
' Holdings Macro
'
'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "UniqueAccountId"
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Ticker"
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Shares"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "MarketValue"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Shares"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=if"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,RC[-2],RC[-3])"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F25399")
    Range("F:F").Select
    Columns("F:F").Select
    Selection.Copy
    Range("F1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Cut
    Range("C1").Select
    ActiveSheet.Paste
    Columns("C:D").Select
    Range("D1").Activate
    Selection.NumberFormat = "0.00"
    Columns("B:B").Select
    Selection.Replace What:="Cash", Replacement:="$$$", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False
End Sub

[david000]

Would it be possible for you to upload a workbook with two sheets that show a before and after type scenario? It doesn't have to have any code or anything.

[Aflatoon]

Try something like this:
[vba]Sub Holdings()
'
' Holdings Macro
'
'
Range("A1").Value = "UniqueAccountId"
Columns("B:B").Delete Shift:=xlToLeft
Range("B1").Value = "Ticker"
Columns("C:C").Delete Shift:=xlToLeft
Range("C1").Value = "Shares"
Range("D1").Value = "MarketValue"
Range("F1").Value = "Shares"
Range("F2").FormulaR1C1 = "=IF(RC[-3]=0,RC[-2],RC[-3])"
Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("F:F").Copy
Range("F1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("F:F").Cut Range("C1")
Columns("F:F").Delete
Columns("I:I").Delete
Columns("C").NumberFormat = "0.00"
Columns("B:B").Replace What:="Cash", Replacement:="$$$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

[/vba]