Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Insert Column with Name in Excel VBA (5 Examples)

In this tutorial, we demonstrate how to use VBA to insert a column with a name in excel. While working in Microsoft Excel, we may sometimes need to add a column to our dataset. This article will guide us on how to use VBA to insert a column into an existing dataset.


Download Practice Workbook

We can download the practice workbook from here.


5 Examples of Using VBA to Insert Column with Name in Excel

This article will show 5 different examples of inserting columns with names using VBA to illustrate the concept clearly. We’ll use the same dataset for all of the examples to make things easier for you.


1. Insert Single Column with Name in Excel Using VBA

First and foremost, we will use VBA to insert a single column with the name in our excel worksheet. In the following dataset, we have sales data for different cities and dates. We’ll use VBA to create a new column that represents the sales regions.

Insert Single Column with Name in Excel Using VBA

Let’s see the steps to perform this action.

STEPS:

  • To begin with, right-click on the active sheet named Single.
  • In addition, select the option ‘View Code’.

Insert Single Column with Name in Excel Using VBA

  • The above action will open a blank VBA code window for that worksheet. Another way to open that code window is to press Alt + F11.
  • Furthermore, type the following 2 codes in that code window:
Sub insert_column()
Range("C1").EntireColumn.insert
End Sub
Sub column_name()
Dim name: name = Split("Region")
Sheet1.Range("C4").Resize(1, UBound(name) + 1) = name
End Sub
  • Then, click the Run button or press the F5 key to run both codes in order.

Insert Single Column with Name in Excel Using VBA

  • Lastly, we get results like the following image.

Here, the first code will create a new column, while the second code will give it a specific name.

Read More: How to Insert a Column to the Left in Excel (6 Methods)


2. Use VBA to Add Multiple Columns with Names in Excel

In the second example, we will use VBA to insert multiple columns with names in excel. To explain this example we will continue with our previous dataset. The screenshot of the dataset that we will use is given below.

Use VBA to Add Multiple Columns with Names in Excel

We will perform this example in the following steps.

STEPS:

  • First, right-click on the active sheet named Multiple.
  • Next, click on the option ‘View Code’.

Use VBA to Add Multiple Columns with Names in Excel

  • It opens a blank VBA code window for the active worksheet. We can also press Alt + F11 to get that code window.
  • Then, insert the following 2 codes in that code window:
Sub insert_multiple_column()
Range("C:D").EntireColumn.insert
End Sub
Sub column_name()
Dim name: name = Split("Region,Area", ",")
Sheet2.Range("C4").Resize(1, UBound(name) + 1) = name
End Sub
  • After that, use the Run button or the F5 key to run both codes in sequence.

Use VBA to Add Multiple Columns with Names in Excel

  • Finally, the above codes give us results like the following image.

Here, the first code inserts two columns, while the second code gives the columns specific names.

Read More: How to Insert Column without Affecting Formulas in Excel (2 Ways)


3. VBA to Remove Formatting While Inserting Column with Name

In earlier examples, the new column we insert keeps the formatting of the other columns. In this example, we will use VBA to insert a column with a name without keeping the formatting. To illustrate this example, we will use the same dataset that we used in previous examples.

VBA to Remove Formatting While Inserting Column with Name

Now, follow the below steps to execute this example.

STEPS:

  • Firstly, right-click on the active sheet named Formatting.
  • Secondly, select the option ‘View Code’.

VBA to Remove Formatting While Inserting Column with Name

  • So, we will get a new blank VBA code window for the active worksheet.
  • Thirdly, input the following 2 codes in that code window:
Sub formatting()
columns(3).EntireColumn.insert
columns(3).ClearFormats
End Sub
Sub column_name()
Dim name: name = Split("Region", ",")
Sheet3.Range("C4").Resize(1, UBound(name) + 1) = name
End Sub
  • Then, hit the F5 key or click the Run button to run both codes in succession.

VBA to Remove Formatting While Inserting Column with Name

  • Lastly, we can see that our dataset has a new column. However, it does not include formatting for other columns.

Here, The first code creates a new column. The second code not only names the new column but also removes its formatting.

Read More: How to Insert a Column Between Every Other Column in Excel (3 Methods)


4. Apply VBA to Add Copied Column with Name in Excel

In this example, we will copy an existing column. Then, we will use VBA to insert the copied column with the name in our excel worksheet. Also, we will use the same dataset for this example that we used earlier.

Apply VBA to Add Copied Column with Name in Excel

Let’s see the steps to perform this example.

STEPS:

  • In the beginning, right-click on the active worksheet named Copy.
  • Next, select the option ‘View Code’.

Apply VBA to Add Copied Column with Name in Excel

  • The above command opens a blank VBA code window for that worksheet. We can also press Alt + 11 to open that code window.
  • Then, type the following code in the blank code window:
Sub insert_column()
Application.CutCopyMode = False
With Worksheets("Copy")
.columns(3).Copy
.columns(5).insert Shift:=xlShiftDown
End With
Application.CutCopyMode = True
End Sub
  • After that, to run the code click on the Run or press the F5 key.

Apply VBA to Add Copied Column with Name in Excel

  • In the end, we can see the results in the following image. The copied column is placed into another column.

Read More: Shortcuts to Insert Column in Excel (4 Easiest Ways)


5. Insert Column with Name Based on Cell Value

In the last example, we will insert a column with a name based on cell value. Look at the below dataset. We will insert a new column named Region in the dataset. The new column will place before the column that contains cell value Sales.

Insert Column with Name Based on Cell Value

Just follow the below steps to do this example.

STEPS:

  • To begin with, select the active sheet named ‘Cell Value’ and right-click on it.
  • In addition, click on the option ‘View Code’.

Insert Column with Name Based on Cell Value

  • The above command opens a new blank VBA code window for the active worksheet. An alternative to open that code window is to press Alt + 11.
  • Next, copy the following 2 codes in that code window:
Sub based_on_cell_value()
Dim k As Integer
Dim x As Integer
x = 2
For k = 2 To 12
If Cells(4, x).Value = "Sales" Then
Cells(4, x).EntireColumn.insert
x = x + 1
End If
x = x + 1
Next k
End Sub
Sub column_name()
Dim name: name = Split("Region")
Sheet5.Range("D4").Resize(1, UBound(name) + 1) = name
End Sub
  • Furthermore, click on the Run or hit the F5 key to run both codes in order.

Insert Column with Name Based on Cell Value

  • Finally, we get results like the following image. A new column named Region is added before the column that contains the cell value Sales.

Here, The first code inserts a new column before the column that contains a specific cell value. The second code gives a name to the newly added column.


Conclusion

In conclusion, this article shows different examples of how to use VBA to insert columns with names in excel. Download the sample worksheet given in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to reply to your message as soon as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo