Run Macro through an entire range

[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

 

[Guest]

Your macro is a bit cumbersome and could probably be lot shorter and more
efficient. What do the Values in Column A Look like (I am guessing
12345?6789)? Let me know that and I can make you something a bit more compact
and efficient...

 

[Guest]

Hi,
Something along these lines but as per Jim's reply, it depends on
the format of the field you are tranforming.

Sub Reformat_SSN()
Dim lastrow As Long, r As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
Cells(r, 2) = Format(Left(Cells(r, 1), 3) & Left(Right(Cells(r, 1), 7),
2) & _
Right(Cells(r, 1), 4), "000-00-0000")
Next r
End Sub

Your macro is a bit cumbersome and could probably be lot shorter and more
efficient. What do the Values in Column A Look like (I am guessing
12345?6789)? Let me know that and I can make you something a bit more compact
and efficient...
--
HTH...

Jim Thomlinson

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