Shift multiple columns

Question

I wrote a macro to insert a selected number of rows in an active cell, but I can't get the same to work with columns as well.

For rows I have

Dim rng As Range
Dim lngIns As Long

lngIns = InNo.Value

If Not refRng.Value = "" Then
    Rows(CStr(Selection.row) & ":" & CStr(CLng(Selection.row) + lngIns - 1)).Select
    Selection.Insert Shift:=xlDown
End If

I tried to do the same for columns (and then combine the two statements with If opt), so the code is

If Not refRng.Value = "" Then
    Columns(CStr(Selection.column) & ":" & CStr(CLng(Selection.column) + lngIns - 1)).Select
    Selection.Insert Shift:=xlRight
End If

but this results in a 1004 Run-time Error (Application-defined or object-defined error). What's wrong here?

score 1 · Accepted Answer · answered Sep 16 '21 at 08:52

1

Use the Range.Resize property and avoid using Select.

If Not refRng.Value = vbNullString Then
    Selection.EntireColumn.Resize(ColumnSize:=lngIns).Insert Shift:=xlRight
End If

Same for the row

If Not refRng.Value = vbNullString Then
    Selection.EntireRow.Resize(RowSize:=lngIns).Insert Shift:=xlDown
End If

answered Sep 16 '21 at 08:52

Pᴇʜ

56,719
10
49
73

Thank you! I wasn't aware that Select should be avoided – maryam Sep 16 '21 at 09:05

Shift multiple columns

1 Answers1