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

How to Insert Column with Excel VBA (4 Ways)

In this article, I’ll show you how you can insert a single column or multiple columns within a data set using VBA in Excel. I’ll give all the VBA codes and explanations with proper examples and illustrations.


How to Insert Column with Excel VBA (Quick View)

Sub VBA_Insert_Single_Column()

ActiveSheet.Range("C4").EntireColumn.Insert

End Sub

Insert Single Column with VBA in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Methods to Insert Column with Excel VBA

Here we’ve got a data set with the names, joining dates, and salaries of a company.

Data Set to Insert Column with Excel VBA

Our objective today is to learn how we can insert one or more columns within this data set using Excel VBA.


1. Insert a Single Column with Excel VBA

First of all, I’ll show you how you can insert a single column within this data set using VBA.

Let’s enter a new column before column B (Joining Date).

The line of code will be:

⧭ VBA Code:

ActiveSheet.Range("C4").EntireColumn.Insert

Insert Single Column with VBA in Excel

⧭ Output:

Run the code and it’ll insert a new column before column C.

⧭ Note:

Instead of C4, you can use any cell reference of column C, if you want to insert a new column before column C.

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


2. Insert Multiple Columns with Excel VBA

Now let’s insert multiple columns within a data set using VBA.

For example, let’s insert 3 columns before column C (Joining Dates).

We have to iterate through a for-loop.

The VBA code will be:

⧭ VBA Code:

Sub VBA_Insert_Multiple_Columns()

Inserted_Columns = 3

For i = 1 To Inserted_Columns
    ActiveSheet.Range("C4").EntireColumn.Insert
Next i

End Sub

VBA Code to Insert Column with Excel VBA

⧭ Output:

Run the code, and it’ll insert 3 empty columns before column C.

Insert Column with Excel VBA

⧭ Note:

Here I’ve put the value of the variable Inserted_Column as 3 because I want to insert 3 columns. You can change it according to your needs.

Read More: Insert Column with Name in Excel VBA (5 Examples)


Similar Readings


3. Insert Single or Multiple Column(s) After a Fixed Interval

This time we’ll learn another very interesting thing. We’ll insert a single column or multiple columns after a fixed interval within our data set.

For example, let’s insert 2 columns after each 1 column of our data set.

The VBA code will be:

⧭ VBA Code:

Sub VBA_Insert_Column_After_a_Interval()

Interval = 1

Inserted_Columns = 2

Count = 0

For i = 1 To ActiveSheet.UsedRange.Columns.Count - 1
    Count = Count + Interval + 1
    For j = 0 To Inserted_Columns - 1
        Count = Count + j
        ActiveSheet.UsedRange.Cells(1, Count).EntireColumn.Insert
    Next j
Next i

End Sub

⧭ Output:

Run this code. It’ll insert 2 columns after each 1 column of your data set.

⧭ Note:

Here I’ve put the value of the variables Interval as 1 and Inserted_Column as 3 because I want to insert 3 columns after each interval of 1 column. You can change these according to your needs.

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


4. Insert Single or Multiple Column(s) at Random Places

Finally, we’ll learn to insert column (s) in random places within our data set.

Let’s insert two columns in each of the 2nd and 4th positions of our data set.

The VBA code will be:

⧭ VBA Code:

Sub Insert_Columns_Randomly()

Positions = "2,4"

Inserted_Columns = 2

Positions = Split(Positions, ",")

Count = 0

For i = 0 To UBound(Positions)
    For j = 0 To Inserted_Columns - 1
        Count = Count + j
        ActiveSheet.UsedRange.Cells(1, Positions(i) + Count).EntireColumn.Insert
    Next j
Next i

End Sub

VBA Code to Insert Column with Excel VBA

⧭ Output:

Run this code and you’ll find 2 columns inserted in each of the 2nd and 4th positions of our data set.

⧭ Note:

Here I’ve put the value of the variables Positions as “2,4” and Inserted_Column as 2 because I want to insert 2 columns in each of the 2nd and 4th positions of my data set. You change these according to your needs.

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


Things to Remember

In a few of the above codes, I have used the UsedRange property of VBA to access all the used cells of my worksheet. Click here to know the UsedRange property of VBA in detail.


Conclusion

Using these methods, you can insert a single column or multiple columns within your data set using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo