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.
Sub VBA_Insert_Single_Column()
ActiveSheet.Range("C4").EntireColumn.Insert
End Sub
Here we’ve got a data set with the names, joining dates, and salaries of a company.
Our objective today is to learn how we can insert one or more columns within this data set using Excel VBA.
1. Inserting 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
⧭ 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. Inserting 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
⧭ Output:
Run the code, and it’ll insert 3 empty columns before column C.
⧭ 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.
Similar Readings
- Insert Column with Name in Excel VBA
- Excel Fix: Insert Column Option Greyed out
- Cannot Insert Column in Excel
3. Adding 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
4. Inserting 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
⧭ 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.
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
