VBA Eval

[ctk0209]

I have a large file list that needs certain rows shifted one cell to the right. Rows with only data in the A column do not need to be shifted. My current code doesn;t work.

Sub shift_right()
If ActiveCell.Offset(0, 1).Value <> "NULL" Then
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End If
End Sub

The files looks like this:

Agnes
Elizabeth (16322BA)	289
Frances M. (16322BF)	290
Harriett E. (16322BD)	290

The goal is for it to look like this:

Agnes
	Elizabeth (16322BA)	289
	Frances M. (16322BF)	290
	Harriett E. (16322BD)	290

Only move rows right if column B has data.
Typically I have 4-5 files with gt 21,000 lines.

I want to select all of column A and have the macro update the list.

 

[Micron]

Nothing is equal to Null and certainly, not "Null", which is literal text. VBA has the IsNull function which I think you can use in Excel, or you can try comparing the cell value to an empty string ( "" ). So maybe
If Not IsNull(ActiveCell.Offset(0, 1) Then

I presume that the activecell will be in the 2nd column that you're showing in the first pic.

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this with a copy of your workbook.
There is no need to select anything first.

Sub Shift_Right()
  Dim rA As Range
  
  Application.ScreenUpdating = False
  For Each rA In Columns("B").SpecialCells(xlConstants).Areas
    rA.Offset(, -1).Insert Shift:=xlToRight
  Next rA
  Application.ScreenUpdating = True
End Sub

 

[kevin9999]

Another option...

Option Explicit
Sub Shift_to_Right()
    Dim a, b, i As Long
    Application.ScreenUpdating = False
    Range("A:A").ColumnWidth = 10: Range("B:B").ColumnWidth = 20: Range("C:C").ColumnWidth = 8
    a = Range("A1", Cells(Rows.Count, "B").End(xlUp))
    ReDim b(1 To UBound(a, 1), 1 To 3)
    For i = 1 To UBound(a, 1)
        If a(i, 2) = "" Then
            b(i, 1) = a(i, 1)
        Else
            b(i, 1) = "": b(i, 2) = a(i, 1): b(i, 3) = a(i, 2)
        End If
    Next i
    Range("A1").Resize(UBound(a, 1), 3).Value = b
End Sub

 

[Peter_SSs]

A simpler version of my previous suggestion

Sub Shift_Right_v2()
  Columns("B").SpecialCells(xlConstants).Offset(, -1).Insert Shift:=xlToRight
End Sub

 

[ctk0209]

Thank you, your solution worked fantastic.
Saved several hours of tedious post-processing.
I'll be using in many times over the next year.
Best regards, Clark K.
P.S. Thank you for your insight on VBA for me.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

Hint for the future though: Since several suggestions have been made by several people, we do not know which suggestion you are referring to or who you are addressing.