Excel VBA Insert Column
In excel if we want to insert a column then there are multiple ways of doing it. We can click right and select Insert Column from the drop-down menu list or using Ctrl + Shift + Plus (+) Key together. But what if we automate this process as well by creating a macro through VBA? This is quite helpful when we are working on creating a macro of huge coding structure or adding a column or multiple columns which is also a task to perform. VBA has an Entire Column and Insert function is used for inserting a new column.
How to Insert Column in Excel VBA?
We will learn how to insert a column in excel using VBA code with few examples.
Example #1 – VBA Insert Column
We have a table of 2 columns and 11 rows as shown below. Now we will automate the process of inserting columns in different ways.
For this, follow the below steps:
Step 1: First insert a new module in VBA, from the Insert menu option as shown below.
Step 2: Now start a subcategory in the name of performed function like VBA Insert Column or in any other name as shown below.
Code:
Sub VBAColumn1() End Sub
Step 3: First, select the range of cells or complete columns where we want to insert a column followed by Insert command as shown below. The Insert command will enable to insert any row or column or a cell in selected Range column or cell.
Code:
Sub VBAColumn1() Range("B:B").Insert End Sub
Step 4: Now select the cell range as a reference from where we want to insert a column as shown below.
Code:
Sub VBAColumn1() Range("B:B").Insert Range ("B4") End Sub
Step 5: As discussed in explanation, we will use the Entire Column with function Insert as shown below. This will enable the column to get inserted.
Code:
Sub VBAColumn1() Range("B:B").Insert Range("B4").EntireColumn.Insert End Sub
Step 6: Now compile the code by pressing the F8 functional key and then click on the Play button to run the complete code which is below the menu bar as shown below. We will notice between column1 and column2, two new blank columns will get inserted as shown below.
Example #2 – VBA Insert Column
There is another method of inserting a column. This method includes a combination of various commands.
Step 1: Insert a new Module from the Insert menu.
Step 2: And write a subcategory in the name of the Performed function as shown below or in any other name.
Code:
Sub VBAColumn2() End Sub
Step 3: With the help of dimension variable DIM with any alphabet or name define it as Range let’ say Column as shown below.
Code:
Sub VBAColumn2() Dim Column As Range End Sub
Step 4: Now following the Range, use a Set function with defined dimension Column as shown below, followed by equal sign (“=”).
Code:
Sub VBAColumn2() Dim Column As Range: Set Column = End Sub
Step 5: Now use Application function which is built-in VBA with defined Range of complete Column B.
Code:
Sub VBAColumn2() Dim Column As Range: Set Column = Application.Range("B:B") End Sub
Step 6: Now use Column variable with Insert function as shown below.
Code:
Sub VBAColumn2() Dim Column As Range: Set Column = Application.Range("B:B") Column.Insert End Sub
Step 7: We can shift to insert a column to right or left direction with the help of Shift command. Normally column is inserted to Right direction always from the reference selected cell or column.
Code:
Sub VBAColumn2() Dim Column As Range: Set Column = Application.Range("B:B") Column.Insert Shift:=xlShiftToRight, End Sub
Step 8: At last select origin reference format from the right or below which is for Column or Row depending on the type of insert.
Code:
Sub VBAColumn2() Dim Column As Range: Set Column = Application.Range("B:B") Column.Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow End Sub
Step 9: Now it is completed. We can compile the code and run it. We will see a new column between Colum1 and Column2 will get added as shown below.
Example #3 – VBA Insert Column
This example is another easiest way to insert a column in VBA.
Step 1: Insert a new Module
Step 2: Add a subcategory in the name of VBA Insert Column as shown below.
Code:
Sub VBAColumn3() End Sub
Step 3: First select the column or cell reference where we want to add a column with the help of Select as shown below.
Code:
Sub VBAColumn3() Columns("B:B").Select End Sub
Step 4: In the second line, use Selection command along with Insert function as shown below.
Code:
Sub VBAColumn3() Columns("B:B").Select Selection.Insert End Sub
Step 5: At last select the position where we want to shift the inserted new column, either right or left with the help of Shift function. The selection of placing a new column is all up to the individual’s requirement.
Code:
Sub VBAColumn3() Columns("B:B").Select Selection.Insert Shift:=xlToRight End Sub
Step 6: This completes our code. Now we can compile the code to find if there is any error. If there is no error found then run the code. We will see a new column will be added between column1 and column2 as shown below.
Example #4 – VBA Insert Column
In this example, we will learn how to add new columns after each already standing column.
Step 1: Now insert a module from the insert menu.
Step 2: After that add subcategory in the name of VBA Insert Column with a sequence of numbers.
Code:
Sub VBAColumn4() End Sub
Step 3: Now use DIM to define a variable in any name. We have used Column as Integer (In case of numbers are used)
Code:
Sub VBAColumn4() Dim Column As Integer End Sub
Step 4: To a column after each standing filled column, we will select and use Column(2) to add a new column as shown below.
Code:
Sub VBAColumn4() Dim Column As Integer Columns(2).Select End Sub
Step 5: Open a For-Next loop, where we will write the conditions to insert a column after one another as shown.
Code:
Sub VBAColumn4() Dim Column As Integer Columns(2).Select For Next End Sub
Step 6: As we have only 2 columns in our sample data, so we could add only a new column of those columns only. So we are using 0 to 2 range of sequence columns.
Code:
Sub VBAColumn4() Dim Column As Integer Columns(2).Select For Column = 0 To 2 Next End Sub
Step 7: With the help of ActiveCell function use EntireColumn to insert as shown below.
Code:
Sub VBAColumn4() Dim Column As Integer Columns(2).Select For Column = 0 To 2 ActiveCell.EntireColumn.Insert Next End Sub
Step 8: With the help of ActiveCell function, use Offset to place the newly added column. We are using 0 and 2nd position.
Code:
Sub VBAColumn4() Dim Column As Integer Columns(2).Select For Column = 0 To 2 ActiveCell.EntireColumn.Insert ActiveCell.Offset(0, 2).Select Next End Sub
Step 9: Now compile the code and run it, if no error found. We will see, after column1 and column2, new columns are added.
Pros of Excel VBA Insert Column
- It is useful as quickly adding a new column in any range of cells.
- It is easy to implement even if the structure of the code is huge.
- If we are working on a huge code, a new column becomes a need to complete the code, using the above examples will make the task easy.
Things to Remember
- If you are using example-4, then remember to select column range till the table is applicable.
- Saving the file in macro enable excel, saves the code.
- Insert only applicable and required columns to avoid un-necessary columns to get added.
Recommended Articles
This is a guide to VBA Insert Column. Here we discuss how to insert Column in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –