How to insert specific number of columns in Excel

Inserting new columns is the easiest way to add new fields into your worksheet area. Since Excel inserts columns by copying the formatting of the previous column, it is usually more practical than moving bottom cells to open up space for new data. Thanks to VBA, we can insert columns dynamically. In this guide, we’re going to show you how to insert specific number of columns in Excel.

Download Workbook

Preparation

First, you need to open the VBA (Visual Basic for Applications) window and add a module.

1. Press Alt + F11 to open the VBA window
2. In the VBA window, click Insert on the toolbar
3. Click the Module option
   

Macro logic

The logic behind of inserting rows is controlled by two elements:

1. The column before which you want to insert new columns. You can think of this as the anchor column.
2. Number of columns.

Once these elements are set, the macro will select the number of columns, starting with the anchor column and run Insert method.

Range(Columns(AnchorColumn), Columns(AnchorColumn + ColumnCount - 1)).Insert

You can identify these elements in the code with set values, gather this information dynamically such as from the active cell, or through user input.

AnchorColumn = 3   'Static
AnchorColumn = ActiveCell.Column  'Selected Cell
AnchorColumn = Application.InputBox(Prompt:="Please select a cell", Type:=8).Column   'User input

Select the one that best fits your use case. Let's now see the code block.

How to insert specific number of columns

The following code inserts a specific number of columns before the selected cell.

Sub InsertColumns1()

    'Define variables

    Dim AnchorColumn As Integer, ColumnCount As Integer

    'Set variables

    AnchorColumn = ActiveCell.Column  'Selected Cell

    ColumnCount = Application.InputBox(Prompt:="Please enter how many columns you want to insert", Type:=1)   'User input

    'Insert columns

    Range(Columns(AnchorColumn), Columns(AnchorColumn + ColumnCount - 1)).Insert

End Sub

The next and final sample code will insert static number of columns before the user-prompted cell.

'This macro will insert static number of columns before the user-prompted cell

Sub InsertColumns2()

    'Define variables

    Dim AnchorColumn As Integer, ColumnCount As Integer

    'Set variables

    AnchorColumn = Application.InputBox(Prompt:="Please select a cell", Type:=8).Column   'User input

    ColumnCount = 3   'Static

    'Insert columns

    Range(Columns(AnchorColumn), Columns(AnchorColumn + ColumnCount - 1)).Insert

End Sub