PDA

View Full Version : VBA code edit to populate to end of data



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

david000
02-04-2014, 10:40 PM
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
02-05-2014, 03:02 AM
Try something like this:
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:D").NumberFormat = "0.00"
Columns("B:B").Replace What:="Cash", Replacement:="$$$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub