How to insert specific number of rows in Excel

Inserting new rows is the easiest way to add new fields into your worksheet area. Since Excel inserts rows by copying the formatting of the previous row, it is usually more practical than moving bottom cells to open up space for new data. Thanks to VBA, we can insert rows dynamically. In this guide, we’re going to show you how to insert specific number of rows 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 row above which you want to insert new rows. You can think of this as the anchor row.
2. Number of rows.

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

Range(Rows(AnchorRow), Rows(AnchorRow + RowCount - 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.

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

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

How to insert specific number of rows

The following code requires the user select a cell in the anchor row.

Sub InsertRows1()

    'Define variables

    Dim AnchorRow As Integer, RowCount As Integer

    'Set variables

    AnchorRow = ActiveCell.Row  'Selected Cell

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

    'Insert rows

    Range(Rows(AnchorRow), Rows(AnchorRow + RowCount - 1)).Insert

End Sub

The next and final sample code will insert a specific number of rows above the user-prompted cell.

'This macro will insert static number of rows above the user-prompted cell

Sub InsertRows2()

    'Define variables

    Dim AnchorRow As Integer, RowCount As Integer

    'Set variables

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

    RowCount = 3   'Static

    'Insert rows

    Range(Rows(AnchorRow), Rows(AnchorRow + RowCount - 1)).Insert

End Sub