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.
Preparation
First, you need to open the VBA (Visual Basic for Applications) window and add a module.
- Press Alt + F11 to open the VBA window
- In the VBA window, click Insert on the toolbar
- Click the Module option
Macro logic
The logic behind of inserting rows is controlled by two elements:
- The row above which you want to insert new rows. You can think of this as the anchor row.
- 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