Table of Contents
Using VBA, you can use the Insert method to insert multiple columns into a worksheet. This method requires passing the number of columns to be inserted, the start column and the end column. You can also specify the position of the new columns and whether to insert them before or after the specified column. Additionally, you can set parameters to control the formatting of the new columns. Once the columns are inserted, you can then add data as necessary.
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 .