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
Suppose I currently have cell A3 selected in my worksheet.
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.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Find Last Used Row
VBA: How to Count Number of Rows in Range
VBA: How to Count Number of Used Columns
