Normally in excel worksheet we have two different methods to add or insert columns one being the keyboard shortcut and another by using the right-click and insert method but in VBA we have to use insert command and entire column statement to add any column together, the trick to this is that if we need to insert a single column we give a single column reference but for the multiple columns we give multiple column references.
Insert Columns in Excel using VBA
As part of the VBA code, we may need to insert a few columns in our datasheet to fit our needs. Inserting column is the easy shortcut key in the worksheet by pressing Ctrl + but how about inserting a new column through VBA code. In this article on “VBA Insert Column”, we will show you how to insert a new column and will show you different scenarios examples.
How to Insert Columns in Excel Worksheet using VBA?
To insert new column we need to identify after which column we need to insert, without telling after which column we need to insert how VBA can understand the logic.
For example, if you want to insert the column after column B then you can construct the code like this.
Note: The reason why I have used C to insert a column after because the selected cell will be shifted to the right side.
After the columns are specified we need to access the “Entire Column” property.
Then we need to choose the “Insert” method.
This will insert the column after column C and the existing column C will be moved to D.
Example #1 – Insert Columns Using Range Object in VBA
Now, look at the way of inserting the column using RANGE object. For this consider the below data for an example.
Now I want to VBA insert the column after the A column, follow below steps to construct the code.
Step 1: Start off the Sub Procedure.
Step 2: Open Range object.
Sub ColumnInsert_Example1() Range( End Sub
Step 3: Now mention the column in double quotes.
Sub ColumnInsert_Example1() Range ("B:B") End Sub
Step 4: Now choose Entire Column property.
Sub ColumnInsert_Example1() Range("B:B").ent End Sub
Step 5: After selecting the Entire Column property, choose “Insert” method.
Sub ColumnInsert_Example1() Range("B:B").EntireColumn.ins End Sub
Now your code looks like this.
Sub ColumnInsert_Example1() Range("B:B").EntireColumn.Insert End Sub
Run the code it will insert the new B column.
Example #2 – Insert Multiple Columns
For example, if you want to insert two new columns after column A then we need to mention two column address.
Sub ColumnInsert_Example1() Range("B:C").EntireColumn.Insert End Sub
The above code will insert two columns after column A.
Example #3 – With “Insert” Only Method
We can insert a new column by using “Insert” the only method without accessing the Entire Column property. For this let’s understand the “insert” method syntax.
[Shift]: When we insert the new column whether we need the columns to shift to the right side or to shift to the downside in case of rows. Here we can use two options “xlToRight” and “xlDownTo”
[Copy Origin]: This will specify the format for the newly inserted column. Whether you need the format from the left side of the column or from the above cells. Here we can use two options “xlFormatFromLeftOrAbove” and “xlFormatFromRightOrBelow”.
Below is the example code for you.
Sub ColumnInsert_Example1() Range("B:B").Insert Shift:=xlToRight, Copyorigin:=xlFormatFromLeftOrAbove End Sub
Example #4 – Insert Columns Using COLUMNS Property in VBA
We have seen how to VBA insert columns using RANGE object, now we will how we can insert columns using “COLUMNS” property.
Open the COLUMNS property.
Sub ColumnInsert_Example2() Columns( End Sub
We can specify the column in two ways here. One is using as usual alphabetic references and another one is using column numbers.
If you want to insert the column after the column A then the code will be COLUMNS(“B”). If you are using numerical reference then the code will be COLUMNS(2).
Sub ColumnInsert_Example2() Columns("B"). End Sub
Now the problem for you arises because when we use COLUMNS property we don’t get to access to intellisense list.
Sub ColumnInsert_Example2() Columns("B").Entir End Sub
Here we need to sure of what we are doing. So this is the reason I have showed you the RANGE object first.
Sub ColumnInsert_Example2() Columns("B:B").EntireColumn.Insert End Sub
Example #5 – Insert Alternative Columns
Assume you have the data like the below image.
If you want to insert new columns after every alternate row, then we need to use VBA loops. Below code it the tailor-made the code for you.
Sub ColumnInsert_Example3() Dim k As Integer Columns(2).Select For k = 2 To 8 ActiveCell.EntireColumn.Insert ActiveCell.Offset(0, 2).Select Next k End Sub
This will insert the column like this.
Example #6 – Insert Column Based on Cell Value
We can also insert a column based on cell value as well. For example, look at the below data.
Here I want to insert the column if the first-row cell value is “Year” and my data should like this after inserting new columns.
Use the below code to perform this task.
Sub ColumnInsert_Example4() Dim k As Integer Dim x As Integer x = 2 For k = 2 To 12 If Cells(1, x).Value = "Year" Then Cells(1, x).EntireColumn.Insert x = x + 1 End If x = x + 1 Next k End Sub
You can download this VBA Insert Columns Excel here. VBA Insert Columns Excel Template
This has been a Guide to VBA Insert Columns. Here we will show you how to insert a single/multiple columns under different scenarios along with examples and downloadable excel template. Below are some useful excel articles related to VBA –