CODEWITHSUNDEEP
vbaexcel
Allie
Allie

Reputation: 1

Excel VBA: Select the column next to the column with data and then insert 3 columns

Sub New
ActiveSheet.Range(“c9”).End(xlRight).Offset(1,0).Select
Selection.Insert Shift:xlToRight
Selection.Insert Shift:xlToRight
Selection.Insert Shift:xlToRight
End Sub

This doesn't work at all and gives me an error. Any help would be greatly appreciated!

Thanks!

Upvotes: 0

Views: 3373

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

You can replace your entire code with 1 line:

ActiveSheet.Range("C9").Offset(0, 1).Resize(, 3).EntireColumn.Insert

The first part ActiveSheet.Range("C9").Offset(0, 1) you select the cell on the right side of Cell "C9".

The second part .Resize(, 3).EntireColumn.Insert you insert 3 columns at once on the right side (instead of repeating the same line 3 times)

In case you meant to find the last column in row 9 with data, as in Range("C9").End(xlRight), use the code below:

With ActiveSheet
    ' find last column with data in row 9
    LastColumn = .Cells(9, .Columns.Count).End(xlToLeft).Column

    .Range(Cells(9, LastColumn), Cells(9, LastColumn)).Offset(0, 1).Resize(, 3).EntireColumn.Insert
End With

Upvotes: 2

Related Questions