How to Insert a Specific Number of Rows at Once in Excel
Inserting rows in Excel worksheet is a common task that many users do every day. inserting a single row is simple and straightforward, but inserting multiple rows in the right places within your worksheet can sometimes be challenging. In this detailed guide, we’re going to show you how to insert specific number of rows in Excel worksheet using VBA Code.

By the end of this tutorial, you’ll be able to insert multiple rows in excel worksheet with just one click.
Steps to automate the process of inserting Multiple rows in Excel Worksheet at once using VBA
Open your Excel Worksheet

Press ALT + F11 Shortcut Key to Open VBA Editor
Create a new module: from the toolbar (in VBA Editor) click on insert, then click on module option.

Copy and paste one of the following code snippet into the module.
Sub insertRows1()
Dim i As Long
Dim j As Variant
j = InputBox("How many rows would you like to insert?", "Insert Rows")
If j = "" Then
j = 1
End If
For i = 1 To j
ActiveCell.Rows.EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next
End Sub
OR
Sub InsertRows2()
Dim AnchorRow As Integer, RowCount As Integer
AnchorRow = ActiveCell.Row
RowCount = Application.InputBox(Prompt:="How many rows would you like to insert?", Type:=1)
Range(Rows(AnchorRow), Rows(AnchorRow + RowCount - 1)).Insert
End Sub
OR
Sub InsertRows3()
Dim AnchorRow As Integer, RowCount As Integer
AnchorRow = Application.InputBox(Prompt:="Please select a cell", Type:=8).Row
RowCount = 3
Range(Rows(AnchorRow), Rows(AnchorRow + RowCount - 1)).Insert
End Sub

After pasting the code press CTRL+S to save the Macros/VBA Code and Close the VBA Editor.
To execute the Macro press ALT+F8, Macros Dialog Box will open, containing list of Macros, select your macro and click on RUN Button.

I hope this tutorial will help to insert specified number of rows in excel worksheet using VBA.
If you have any query, suggestion or feedback feel free to contact us using the details provided on our contact us page.
Thank you for your feedback & support.