VBA to Insert Row in Excel

When working with datasets, the need to insert rows often arises, whether for better organization or to make space for new data, headers, or subheadings.

In this tutorial, I will show you various ways of inserting a row or multiple rows in Excel using VBA.

Table of Contents

VBA to Insert a Single Row in Excel

In this section, I will show you examples of VBA to insert a single row in a dataset using the ‘Range.Insert’ method.

The ‘Range.Insert’ Method

The ‘Range.Insert’ method inserts a cell or a range of cells into the worksheet and moves other cells away to make room for the inserted cells.

The ‘Range.Insert’ method takes two optional arguments of Variant data type. The arguments are described below:

Argument	Description
Shift	It indicates which way to shift cells. The argument can be either ‘xlShiftToRight’ or ‘xlShiftDown.’ If this argument is omitted, Excel decides how to move the cells based on the range’s shape.
CopyOrigin	It specifies from where to copy the format for the inserted cells. It can take the default ‘xlFormatFromLeftOrAbove’ constant or the constant ‘xlFormatFromRightOrBelow.’

VBA to Insert a Single Row in Excel With Formatting Copied From the Row Above

Suppose you have the dataset below on Sheet1 and want to insert a row at row 3 with formatting copied from the row above it, row 2:

You can use the Excel VBA code below to insert a row at row 3 with formatting copied from the row above it:

Sub InsertRow()
Worksheets("Sheet1").Rows(3).Insert Shift:=xlShiftDown
End Sub

When you execute the code, it pushes row 3 down and inserts a row at row 3, with formatting copied from row 2:

Note: You can see the copied formatting when you enter data in the inserted row.

Also read: Delete Blank Rows Using VBA

VBA to Insert a Row With Formatting Copied From the Row Below

Let’s say you have the dataset below on Sheet2 and want to insert a row at row 2:

If you omit the ‘CopyOrigin’ parameter from the VBA code, the default ‘xlFormatFromLeftOrAbove’ constant will apply, and the inserted row will have the formatting of the header row as shown below:

You can use the VBA code below to insert a row above row 2 with formatting copied from the row below (row 3) instead of from the header row:

Sub InsertRowFormattingBelow()
Worksheets("Sheet2").Rows(2).Insert xlShiftDown, _
xlFormatFromRightOrBelow
End Sub

When you run the subroutine, it inserts a row at row 2 with formatting copied from the row below it:

VBA to Insert a Row and Clear its Formatting

The ‘CopyOrigin’ parameter of the ‘Range.Insert’ method does not have a value for clearing formatting. So, when you want to insert a row and clear its formatting, use the ‘ClearFormats’ method.

Suppose you have the dataset below on Sheet2 and want to insert a row at row 5 and clear its formatting.

The example code below inserts a row at row 5 of the dataset and clears its formatting:

Sub InsertRowClearFormats()
Worksheets("Sheet2").Rows(5).Insert xlShiftDown
Worksheets("Sheet2").Rows(5).ClearFormats
End Sub

When you execute the subroutine, it inserts a row at row 5 and clears its formatting copied by default from row 4, which includes the yellow fill color:

Also read: Autofill to Last Row Using VBA in Excel

VBA to Insert a Row in Excel Using the ‘Range.EntireRow’ Property

In the previous examples, the VBA code called the ‘Range.Insert’ method upon the row object returned by the ‘Worksheet.Rows’ property. In this example, we use Excel VBA code that calls the ‘Range.Insert’ method upon the row object returned by the ‘Range.EntireRow’ property.

Let’s say you have the dataset below on Sheet2 and want to insert a row on top of cell A4.

You can use the Excel VBA code below to insert a row on top of cell A4:

Sub InsertOneEntireRow()
Worksheets("Sheet1").Range("A4").EntireRow.Insert
End Sub

When you execute the code, it inserts a row above cell A4 and pushes row 4 down to become row 5:

In this code, the ‘Range.Insert’ method is called upon the entire row of cell A4 and inserts a row above it.

Also read: VBA to Hide Rows Based On Cell Values

VBA to Insert Row After Every Other Row

Suppose you have the dataset below on the active sheet:

You can use the Excel VBA code below to insert a row after every other row with data in the dataset to create room for more data or for visual organization:

Sub InsertRowAfterOtherRow()
Dim otherRow As Long
Dim i As Long
Dim numRowsToInsert As Long
otherRow = 2
numRowsToInsert = 1
Do While Cells(otherRow, 1).Value <> ""
For i = 1 To numRowsToInsert
Rows(otherRow + 1).Insert
Next i
otherRow = otherRow + numRowsToInsert + 1
Loop
End Sub

When you run the code, it inserts a row after every other row in the dataset (starting at row 2), as shown below:

Also read: VBA to Delete Rows Based On Cell Value

VBA to Insert a Cut Row

Sometimes, you may need to cut a row in a dataset and insert it at another row.

Let’s say you have the dataset below on Sheet2 and want to cut row 5 and insert it at row 3:

Notice that row 5 contains ‘Dell AIO OptiPlex 7450’ in cell A5.

The following VBA code will do the job for you:

Sub InsertCutRow()
With Worksheets("Sheet2")
    .Rows(5).Cut
    .Rows(3).Insert Shift:=xlDown
End With
End Sub

When you run the subroutine, it cuts row 5 and inserts it at row 3, as depicted below:

VBA to Insert Multiple Rows in Excel

In this section, I will show you VBA examples of inserting multiple rows in Excel.

VBA to Insert Multiple Rows (Hardcoded)

Let’s say you have the dataset below on the active sheet and want to insert three rows above row 3:

You can use the VBA code below to accomplish the task:

Sub InsertHardcodeRows()
ActiveSheet.Range("A3:A5").EntireRow.Insert
End Sub

When you run the code, it inserts three rows above row 3 (which then becomes row 6), as shown below:

In this example, we have to specify in the code a range whose count is equivalent to the count of the rows we want to insert.

The downside of the method in this example is that you have to keep changing the specified range whenever you want to change the number of rows you want to insert.

Also read: VBA to Find Last Row in Excel

VBA to Insert Multiple Rows (Specified by User)

Instead of hardcoding a range whose count is equivalent to the number of rows you want to insert, you can prompt the user to input the number of rows to insert and the row at which to insert the rows as in the code below:

Sub InsertUserSpecifiedRows()
Dim iCount As Variant
Dim iRow As Variant
Dim i As Long
iCount = InputBox(Prompt:="Enter the number of rows to insert")
If iCount = Empty Then
    Exit Sub
End If
iRow = InputBox _
(Prompt:="Enter the row number above which to insert the rows")
If iRow = Empty Then
    Exit Sub
End If
For i = 1 To iCount
    ActiveSheet.Rows(iRow).EntireRow.Insert
Next i
End Sub

When you execute this code, it prompts the user to enter the number of rows to insert and the row above which to insert them. The code uses the FOR NEXT loop to insert the number of rows the user indicates one by one.

In this tutorial, I showed you several examples of VBA for inserting a row or rows in Excel. I hope you found the tutorial helpful.

Other Excel VBA articles you may also like: