L
life.ennui
I have recorded a macro that reformats a social security number, but I
have only performed the function on one row. How do I edit the macro
to run through all rows that are not blank? Here is the code I have so
far:
Columns("B:G").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],7)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],2)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-4],4)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-4],RC[-2],RC[-1])"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+0"
Range("G2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "000-00-0000"
Columns("B:F").Select
Selection.Delete Shift:=xlToLeft
I want to be able to run the macro for an entire worksheet, but right
now it only runs on the first row. Anyone have any help?
Thanks
have only performed the function on one row. How do I edit the macro
to run through all rows that are not blank? Here is the code I have so
far:
Columns("B:G").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],3)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],7)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],2)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-4],4)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-4],RC[-2],RC[-1])"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+0"
Range("G2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "000-00-0000"
Columns("B:F").Select
Selection.Delete Shift:=xlToLeft
I want to be able to run the macro for an entire worksheet, but right
now it only runs on the first row. Anyone have any help?
Thanks