sg2209
01-24-2018, 11:02 PM
hi Friends ,
i am new to VBA and trying to learn the VBA by going through the Forums, Videos and by recording the Macros , i have recorded the macro to split the First Name, Middle Name and Last name and Concatenate the Last,First ( in this Format).
now i am confused how can i get it convert to the last row , sometimes the data is upto Row 15000 , sometimes 18000 , i am confused with this and every time i need to specify the ranges , please help how should i change my macro to perfect VBA Coding , below is the macro that i have recorded , please help i did try by name the variables , but not succeeded
[Dim myfirstrow As Long, j As Long
Dim mylastrow As Long
'Inserting the Columns
Worksheets("Sample_testing").Range("F1").EntireColumn.Insert
Worksheets("Sample_testing").Range("G1").EntireColumn.Insert
Worksheets("Sample_testing").Range("H1").EntireColumn.Insert
Worksheets("Sample_testing").Range("I1").EntireColumn.Insert
'Getting Headers in Inserted Columns
Worksheets("Sample_testing").Range("F1").Value = "First Name"
Worksheets("Sample_testing").Range("G1").Value = "Middle Name"
Worksheets("Sample_testing").Range("H1").Value = "Last Name"
Worksheets("Sample_testing").Range("H1").Value = "Last First Name"
myfirstrow = 1
mylastrow = 25000
Columns("E:E").Select
Columns("B:B").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],SEARCH("" "",RC[-1]))"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=MID(RC[-2], SEARCH("" "", RC[-2]) + 1, SEARCH("" "", RC[-2], SEARCH("" "", RC[-2])+1) - SEARCH("" "", RC[-2])-1)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=RIGHT(RC[-3],LEN(RC[-3]) - SEARCH("" "", RC[-3], SEARCH("" "", RC[-3],1)+1))"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Last,First"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],"","",RC[-3])"
Range("E3").Select
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range("B32829:E32829").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Next m
End With
i am new to VBA and trying to learn the VBA by going through the Forums, Videos and by recording the Macros , i have recorded the macro to split the First Name, Middle Name and Last name and Concatenate the Last,First ( in this Format).
now i am confused how can i get it convert to the last row , sometimes the data is upto Row 15000 , sometimes 18000 , i am confused with this and every time i need to specify the ranges , please help how should i change my macro to perfect VBA Coding , below is the macro that i have recorded , please help i did try by name the variables , but not succeeded
[Dim myfirstrow As Long, j As Long
Dim mylastrow As Long
'Inserting the Columns
Worksheets("Sample_testing").Range("F1").EntireColumn.Insert
Worksheets("Sample_testing").Range("G1").EntireColumn.Insert
Worksheets("Sample_testing").Range("H1").EntireColumn.Insert
Worksheets("Sample_testing").Range("I1").EntireColumn.Insert
'Getting Headers in Inserted Columns
Worksheets("Sample_testing").Range("F1").Value = "First Name"
Worksheets("Sample_testing").Range("G1").Value = "Middle Name"
Worksheets("Sample_testing").Range("H1").Value = "Last Name"
Worksheets("Sample_testing").Range("H1").Value = "Last First Name"
myfirstrow = 1
mylastrow = 25000
Columns("E:E").Select
Columns("B:B").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],SEARCH("" "",RC[-1]))"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=MID(RC[-2], SEARCH("" "", RC[-2]) + 1, SEARCH("" "", RC[-2], SEARCH("" "", RC[-2])+1) - SEARCH("" "", RC[-2])-1)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=RIGHT(RC[-3],LEN(RC[-3]) - SEARCH("" "", RC[-3], SEARCH("" "", RC[-3],1)+1))"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Last,First"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],"","",RC[-3])"
Range("E3").Select
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range("B32829:E32829").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Next m
End With