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.

How to Use VBA to Insert Column with Name in Excel: 5 Examples

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.

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

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

• 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: Excel VBA: Cut and Insert Column

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.

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

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

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

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.

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

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

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

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.

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

• The above command opens a blank VBA code window for that worksheet. We can also press Alt + F11 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.

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

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 be placed before the column that contains the cell value Sales.

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

• 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 + F11.
• 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.

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

Download Practice Workbook

We can download the practice workbook from here.

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.