You can use the following syntax to insert multiple columns in Excel using VBA:
Sub InsertMultipleColumns()
Worksheets("Sheet1").Range("B:D").EntireColumn.Insert
End Sub
This particular macro will insert three blank columns in the range D through F of the sheet called Sheet1 and push any existing columns to the right.
The following example shows how to use this syntax in practice.
Example: How to Insert Multiple Columns Using VBA
Suppose we have the following dataset that contains information about various basketball players:
Suppose we would like to insert three blank columns starting at column B.
We can create the following macro to do so:
Sub InsertMultipleColumns()
Worksheets("Sheet1").Range("B:D").EntireColumn.Insert
End Sub
When we run this macro, we receive the following output:
Notice that three blank columns were inserted into column locations B, C and D of this worksheet called Sheet1.
The values that previously existed in columns B, C and D were simply pushed to the right.
Note: You can find the complete documentation for the VBA Insert function here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Find Last Used Row
VBA: How to Count Number of Rows in Range
VBA: How to Count Number of Used Columns
