Hi,

I have searched the forums high and low but due to my limited experience using macros and vba I didn't know how to apply the code to my situation. I recorded a macro to start by inserting a column to the right and apply a formula using the data in column A, pasting the information down, copying and pasting the values back into the first column so the field will be uniform in spaces when it is converted to text. The issue I'm having is if there are blanks, the formula will not go all the way down to the last field. When I recorded the macro I used relative reference because it could be used in multiple columns.

If anyone could help I would greatly appreciate it! Here is my code thus far:

ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),REPT("" "",R1C[-1]),REPT("" "",R1C[-1]-LEN(RC[-1]))&RC[-1])"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(2, 0).Range("A1").Select