VBA Insert Column (Single and Multiple)

In this tutorial, we will look at how to insert a column using a VBA code in Excel. We will also explore what are the different ways to write a macro for this.

Insert a Single Column using VBA

To insert a column using a VBA code, you need to use the “Entire Column” property with the “Insert” method. With the entire column property, you can refer to the entire column using a cell and then insert a new column. By default, it will insert a column before the cell that you have mentioned.

1. First, specify a cell using the range object.
2. Now, enter a dot (.) to get the list of properties and methods.
3. After that, select the “Entire Column” property or type it.
4. In the end, again enter a dot (.) and select the “Insert” method or type it.

Range("A1").EntireColumn.Insert

Your code is ready here to insert a column. Now when you run this code, it will instantly insert a new column before the column A.

Insert Multiple Columns

There are two ways to insert multiple columns in a worksheet that I have found. The first is the same insert method that we have used in the above example. With this, you need to specify a range of columns whose count is equal to the count of the column you want to insert.

Now let’s say you want to insert 5 columns after column C in the case you can use a code like the following.

Range("C:G").EntireColumn.Insert

To be honest, I haven’t found this method quite useful because you need to change the range if you want to change the code itself. So, here’s the second method.

'variables to use in the code
Dim iCol As Long
Dim iCount As Long
Dim i As Long

'to get the number of columns that you want to insert with an input box
iCount = InputBox(Prompt:="How many column you want to add?")

'to get the column number where you want to insert the new column
iCol = InputBox _
(Prompt:= _
"After which column you want to add new column? (Enter the column number)")

'loop to insert new column(s)
For i = 1 To iCount
    Columns(iCol).EntireColumn.Insert
Next i

When you run this code, it asks you to enter the number of columns that you want to add and then the column number where you want to add all those new columns. It uses a FOR LOOP (For Next) to enter the number of columns that you have mentioned.

Insert Columns Based on the Cell Values

If you want to insert columns based on a cell value, then you can use the following code.

Dim iCol As Long
Dim iCount As Long
Dim i As Long

iCount = Range("A1").Value
iCol = Range("B1").Value

For i = 1 To iCount
    Columns(iCol).EntireColumn.Insert
Next i

When you run this macro, it takes count of columns from the cell A1 and the column where you want to add columns from the cell B1.

Insert a Column without Formatting

When you insert a column where the above column has some specific formatting, in that case, the column will also have that formatting automatically. And the simplest way to deal with this thing is to use clear formats. Consider the following code.

Columns(7).EntireColumn.Insert
Columns(7).ClearFormats

When you run the above code, it inserts a new column before the 7^th column. Now, what happens, when you insert a column before the 7^th column that new column becomes the 7^th column, and then the second line of code clear the formats from it.

Insert Copied Column

You can also use the same method to copy a column and then insert it somewhere else. See the following code.

Application.CutCopyMode = False

With Worksheets("Data")
.Columns(5).Copy
.Columns(9).Insert Shift:=xlShiftDown
End With

Application.CutCopyMode = True