CODEWITHSUNDEEP
excelvba
Ali Zubair
Ali Zubair

Reputation: 41

Excel VBA - Insert Multiple Columns at a range

I am trying to insert multiple columns at the range. Below code inserts 1 column at a range stored in variable "startCELL".

Range(startCELL).Offset(0, 1).EntireColumn.Insert

I can insert as many columns I want by repeating below code e.g. if I need 3 columns I can do that by repeating this code 3 times.

Is there a way to insert multiple columns at a specific range using single line of code?

Thanks - I am new learner so I don't know this.

Upvotes: 2

Views: 3250

Answers (2)

n a
n a

Reputation: 143

For the case to insert a column at the left of only specified cells,

- original cells (need to insert in front of B3, C3, D3 at once)

enter image description here

- after insertion

enter image description here

- code

Sub fixColumn()
    Dim idx As Integer, cellStr As String
    With activeSheet
        For idx = 2 To 4
            cellStr = cellStr & .Cells(3, idx).Address(False, False) & ","
        Next
        cellStr = left(cellStr, Len(cellStr) - 1)
        .Range(cellStr).Select
        Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    End With
End Sub

Upvotes: 0

BigBen
BigBen

Reputation: 50008

Use Resize:

Range(startCELL).Offset(,1).Resize(,3).EntireColumn.Insert

Upvotes: 4

Related Questions