Excel VBA code to insert column to left or to right by finding header

insert-column

Why insert column through VBA?

There are many situations you need a new column at the end or in between for adding new data into an existing one. But this macro is a waste if you use only for inserting a column because you can insert column much easily through excel's traditional way by right clicking on the particular column and select Insert.

This code makes sense when you have huge data you want a new column to be dynamically inserted then this code has a significant role.

What does this code do?

The main objective of this code is to insert a column to left or right by searching for a particular header. Suppose if we want to insert a new column before and after the Quantity column, this code searches for the header named "Quantity" and inserts a new column to the left and to the right.

Learning outcome - How to insert a new column, secondly how to find a word.

insert-column-by heading
Insert Column to the Left:

This portion of code inserts a new column before the quantity column. First, we activate the cell A1 and search for the word "Quantity" to find the column number and save it in variable n. once we find the column number of quantity column we can easily insert a new column before that by using entire column insert to insert column and also assign that column with a heading.

Option Explicit
Sub insertbefore()
Dim n As Integer
Range("A1").Activate
n = ActiveCell.EntireRow.Find("Quantity").Column
Cells(1, n).EntireColumn.Insert
Cells(1, n).Value = "Before"
End Sub
Output:
insert-column-by heading

As you can see the above code searched for the header "Quantity" and inserted a new column before that.

Insert Column to the Right:

This portion of code inserts a new column after the quantity column. First, we activate the cell A1 and search for the word "Quantity" to find the column number and save it in variable n. once we find the column number of quantity column add +1 to add after that by using entire column insert to add a new column and also assign that column with a heading.

Option Explicit
Sub insertafter()
Dim n As Integer
Range("A1").Activate
n = ActiveCell.EntireRow.Find("Quantity").Column
Cells(1, n + 1).EntireColumn.Insert
Cells(1, n + 1).Value = "After"
End Sub
Output
insert-column-by heading

As you can see the above code searched for the header "Quantity" and inserted a new column after that.

Alternately you can assign macros to a button and add columns to faster.

Hope you liked this article, if so please share it with your friends.

Try experimenting with these codes to get clarity.

Excel Logo

Download project file

If you want more clarity or demo of this code, please watch the below youtube video.

Detailed Video: Insert Column by finding Header - to right or to the left

Share on:
Most Popular