CODEWITHSUNDEEP
excelvba
JBlack
JBlack

Reputation: 29

VBA : Insert new column to the left of all columns up until the last column

I'm trying to insert a column next to each column starting from D, my issue is that the number of columns will change so I need to identify the last column within the code.

Whenever I run the below it doesn't seem to be identifying the last column correctly. 

Sub InsertColumnsv2()
  Dim iLastCol As Long
  Dim StartCell As Range

  Set StartCell = Range("A1")

  iLastCol = StartCell.SpecialCells(xlCellTypeLastCell).Column

  For colx = 4 To iLastCol Step 2
    ActiveSheet.UsedRange
    iLastCol = StartCell.SpecialCells(xlCellTypeLastCell).Column
    Columns(colx).Insert Shift:=xlToRight
  Next colx
End Sub

Upvotes: 0

Views: 974

Answers (3)

user8753746
user8753746

Reputation:

A better way to solve your insert problem is start from the finish to the start.

Sub InsertColumnsv2()
  Dim iLastCol As Long
  Dim StartCell As Range

  Set StartCell = Range("A1")

  iLastCol = StartCell.SpecialCells(xlCellTypeLastCell).Column

  For colx = iLastCol  To 4 Step -1
      Columns(colx).Insert Shift:=xlToRight
  Next colx
End Sub

Upvotes: 1

Tom
Tom

Reputation: 9878

Try running it backwards instead

Sub InsertColumnsv2()
    Dim iLastCol As Long
    Dim colx As Long

    iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    For colx = iLastCol To 4 Step -1
        Columns(colx).Insert Shift:=xlToRight
    Next colx
End Sub

Upvotes: 3

Shai Rado
Shai Rado

Reputation: 33682

Sine you are adding columns in between, the iLastCol changes, so you need to loop backwards, use Step -1 since you are adding a column each step.

Note: don't rely on ActiveSheet, instead fully qualify your Columns and Cells object using With Worksheets("YourSheetName").

Option Explicit

Sub InsertColumnsv2()

Dim iLastCol As Long, colx As Long

With Worksheets("Sheet1") ' <-- change to your sheet's name
    iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column ' get the last column for the first row

    For colx = iLastCol To 4 Step -1
        .Columns(colx).Insert Shift:=xlToRight
    Next colx
End With

End Sub

Upvotes: 2

Related Questions