EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Lookup & Reference Functions VBA Insert Column
Secondary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
    • Compare Two Columns in Excel using VLOOKUP
    • VLOOKUP with Different Sheets

VBA Insert Column

By Ashwani JaiswalAshwani Jaiswal

VBA Insert Column

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA Insert Column Excel Template here – VBA Insert Column Excel Template

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.

Module VBA insert

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

Insert column Example 1

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

Insert column Example 1.1

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

Insert column Example 1.2

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

Insert column Example 1.3

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.

VBA insert column

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.

Module VBA insert 2

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

VBA Column 2

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

Insert Dim

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

Set Column

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

Application Range

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

Application Range 2

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

Shift to right

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

Insert column Example 2.7

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.

VBA insert Column 2

Example #3 – VBA Insert Column

This example is another easiest way to insert a column in VBA.

Step 1: Insert a new Module

Module VBA insert 3

Step 2: Add a subcategory in the name of VBA Insert Column as shown below.

Code:

Sub VBAColumn3()

End Sub

VBA column 3

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

Colums Select

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

Selection Insert

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

Shift to right

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.

VBA Insert Column 3

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.

module 4

Step 2: After that add subcategory in the name of VBA Insert Column with a sequence of numbers.

Code: 

Sub VBAColumn4()

End Sub

Insert column Example 4.1

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

Insert column Example 4

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

Insert column Example 4.2

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

Insert column Example 4.4

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

Insert column Example 4.5

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

Insert column Example 4.6

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

Insert column Example 4.7

Step 9: Now compile the code and run it, if no error found. We will see, after column1 and column2, new columns are added.

VBA insert Column 4

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 –

  1. VBA VLOOKUP
  2. VBA Copy Paste
  3. VBA With
  4. VBA Month
Popular Course in this category
All in One Excel VBA Bundle (120+ Courses)
  120+ Online Courses |  500+ Hours |  Verifiable Certificates |  Lifetime Access
4.9
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more