Table of Contents
Using VBA, you can insert multiple rows into an Excel worksheet by looping through a recordset object or an array. When looping through the recordset, you can use the Add method to insert a new row after the last row in the worksheet. When looping through an array, you can use the Insert method to insert a new row at a specified index. Both methods can be used to quickly insert multiple rows into a worksheet.
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.