CODEWITHSUNDEEP
excelvba
maryam
maryam

Reputation: 111

Shift multiple columns

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?

Upvotes: 1

Views: 67

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 1

Related Questions