How to Insert Multiple Rows Using VBA


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

Leave a Reply

Your email address will not be published. Required fields are marked *