bopha99
02-04-2014, 06:27 PM
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
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