You can use the following methods to insert multiple rows in Excel using VBA:

Method 1: Insert Rows into Specific Range

Sub InsertMultipleRows()
Worksheets("Sheet1").Range("5:7").EntireRow.Insert
End Sub

This particular macro will insert three blank rows in the range 5 through 7 of the sheet called Sheet1 and move down any existing rows.

Method 2: Insert Rows Based on Active Cell

Sub InsertMultipleRows()
ActiveCell.EntireRow.Resize(3).Insert Shift:=xlDown
End Sub

This particular macro will insert three blank rows starting from whatever cell you currently have selected in your worksheet and move down any existing rows.

The following examples show how to use each method in practice with the following worksheet in Excel:

Example 1: Insert Rows into Specific Range

We can create the following macro to insert three blank rows in the range 5 through 7 of the sheet called Sheet1 and move down any existing rows:

Sub InsertMultipleRows()
Worksheets("Sheet1").Range("5:7").EntireRow.Insert
End Sub

When we run this macro, we receive the following output:

Notice that three blank rows were inserted into the worksheet in row locations 5 through 7.

The values that previously existed in those rows were simply pushed down.

Example 2: Insert Rows Based on Active Cell

We can create the following macro to insert three blank rows into the worksheet starting from the currently selected cell:

Sub InsertMultipleRows()
ActiveCell.EntireRow.Resize(3).Insert Shift:=xlDown
End Sub

When we run this macro, we receive the following output:

Notice that three blank rows were inserted into the worksheet starting from row 3.

The values that previously existed in those rows were pushed down.