WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Insert Columns

VBA Insert Columns

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 the process of adding columns in excel and will show you different scenarios examples.

VBA Insert Columns

How to Insert Columns in Excel Worksheet using VBA?

We can insert columns in VBA using COLUMNS property and RANGE object. You must why we need columns and range objects to insert a new column.

To insert a 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.

Columns(“C”)

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.

Columns(“C”).EntireColumn

Then we need to choose the “Insert” method.

Columns(“C”).EntireColumn.Insert

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 the RANGE object. For this, consider the below data for an example.

VBA Insert Columns Step 1

Now I want to VBA insert the column after the A column, follow the below steps to construct the code.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Step 1: Start off the Sub Procedure.

Step 2: Open Range object.

Code:

Sub ColumnInsert_Example1()

  Range(

End Sub

VBA Insert Columns Step 2

Step 3: Now mention the column in double-quotes.

Code:

Sub ColumnInsert_Example1()

  Range ("B:B")

End Sub

VBA Insert Columns Step 3

Step 4: Now choose the Entire Column property.

Code:

Sub ColumnInsert_Example1()

  Range("B:B").ent

End Sub

VBA Insert Columns Step 4

Step 5: After selecting the Entire Column property, choose the “Insert” method.

Code:

Sub ColumnInsert_Example1()

  Range("B:B").EntireColumn.ins

End Sub

VBA Insert Columns Step 5

Now your code looks like this.

Code:

Sub ColumnInsert_Example1()

  Range("B:B").EntireColumn.Insert

End Sub

Run the code. It will insert the new B column.

VBA Insert Columns Step 6

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 addresses.

Code:

Sub ColumnInsert_Example1()

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

End Sub

The above code will insert two columns after column A.

Insert Multiple Columns

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.

Expression.Insert([Shift],[Copy Origin])

[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 the RANGE object; now, we will show we can insert columns using the “COLUMNS” property.

Open the COLUMNS property.

Code:

Sub ColumnInsert_Example2()

  Columns(

End Sub

Using VBA Columns Property 1

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 column A, then the code will be COLUMNS(“B”). If you are using numerical reference, then the code will be COLUMNS(2).

Code:

Sub ColumnInsert_Example2()

  Columns("B").

End Sub

Using VBA Columns Property 1-1

Now the problem for you arises because when we use COLUMNS property, we don’t get to access the IntelliSense list.

Code:

Sub ColumnInsert_Example2()

  Columns("B").Entir

End Sub

Using VBA Columns Property 1-2

Here we need to sure of what we are doing. So this is the reason I have showed you the RANGE object first.

Code:

Sub ColumnInsert_Example2()

  Columns("B:B").EntireColumn.Insert

End Sub

Example #5 – Insert Alternative Columns

Assume you have the data like the below image.

Alternative Columns 1

If you want to insert new columns after every alternate row, then we need to use VBA loops. The below code it tailor-made the code for you.

Code:

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.

Alternative Columns 1-1

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.

Based on Cell Value 1

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.

Based on Cell Value 1-1

Use the below code to perform this task.

Code:

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

Recommended Articles

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 a downloadable excel template. Below are some useful excel articles related to VBA –

  • Count in Excel VBA
  • VBA Union
  • VBA ENUM
  • Delete Column using VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More