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

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


" wrote:

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

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

"Jim Thomlinson" wrote:

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


" wrote:

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