Shift multiple columns-CodePudding

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?

CodePudding user response：

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