How to Use VBA to Insert Rows in Excel – 11 Methods

Method 1 – Applying VBA to Insert a Single Row Based on Cell Text Value in Excel

Consider the following dataset.

Steps:

• Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Select Insert -> Module.

• Enter the following code.

Sub InsertRowsBasedonCellTextValue()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
With ActiveSheet
    'Define First and Last Rows
    FirstRow = 1
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    'Loop Through Rows (Bottom to Top)
    For Row = LastRow To FirstRow Step -1
        If .Range("B" & Row).Value = "Insert Above" Then
            .Range("B" & Row).EntireRow.Insert
        End If
    Next Row
End With
End Sub

• Press F5 or click Run -> Run Sub/UserForm. You can also click the small Play to run the macro.

A new row right was inserted above the cell containing “Insert Above”.

Read More: Excel VBA: Insert Row with Values

Method 2 – Using a Macro to Embed Rows Based on Cell Numeric Values in Excel

Consider the following dataset.

.

Steps:

• Open the Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertBlankRowsBasedOnCellNumericValue()
    Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long
        Col = "C"
        StartRow = 1
        BlankRows = 1
            LastRow = Cells(Rows.Count, Col).End(xlUp).Row
            Application.ScreenUpdating = False
            With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "99" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub

• Run the code and a new row will be inserted above the cell containing 99.

Method 3 – Inserting Multiple Rows Based on a Cell Value Using a VBA Macro in Excel

This is the dataset.

Steps:

• Open Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertRowBasedonValue()
Dim iRow As Long
Dim iCount As Long
Dim i As Long
iCount = Range("A4").Value
iRow = Range("A3").Value
For i = 1 To iCount
Rows(iRow).EntireRow.Insert
Next i
End Sub

• Run the code. It counts the rows between A4 and the row in which you want to add rows starting from A3. As the value in A4  is 3, 3 rows will be inserted.

Read More: Insert Rows in Excel Based on Cell Value with VBA

Method 4 – Utilizing a Macro to Add Multiple Rows in a Range in Excel

Steps:

• Open Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertMultipleRows()
'insert multiple rows as rows 6, 7 and 8
Worksheets("Rows").Range("6:8").EntireRow.Insert
End Sub

• Run the code and  multiple rows (6, 7, and 8) will be inserted.

Method 5 – Inserting Multiple Rows Based on Predefined Conditions in Excel

The following dataset contains multiple rows.

A predefined number of rows will be inserted: ( “3 Quantity” of rows of “Name Root” and “Score 100” ).

Steps:

• Open Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertRows()
Qty = Range("E3").Value
Range("B12").Select
ActiveCell.Rows("1:" & Qty).EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Select
Range("B12").Resize(Qty, 2).Value = Range("C3:D3").Value
End Sub

• Run the code and  C3 and D3 will be inserted according to the quantity in E3.

Read More: VBA Macro to Insert Row in Excel Based on Criteria

Method 6 – Embedding Multiple Rows Based on the User Input in Excel

Steps:

• Open Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertRowsfromUserInput()
Dim iRow As Long
Dim iCount As Long
Dim i As Long
iCount = InputBox(Prompt:="How Many Rows to Insert?")
iRow = InputBox _
(Prompt:="Where to Insert New Rows? (Enter the Row Number)")
For i = 1 To iCount
Rows(iRow).EntireRow.Insert
Next i
End Sub

• Run the macro.
• In the pop-up window, enter a row number.
• Click OK.

• In the new window, enter the row address and click OK.

Here, 3 rows in row address 6.

This is the output.

Method 7 – Using a VBA Macro to Enter a Single Row after Each Record in Excel

This is the sample dataset.

Steps:

• Open Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertRow()
Dim i As Long
For i = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 3 Step -1
If Cells(i, "B") <> Cells(i - 1, "B") Then Rows(i).EntireRow.Insert
Next i
End Sub

You can change Cells (lRow – 1, “B”).

• Run the code and a single row will be inserted after each record.

Method 8 – Inserting Multiple Rows from an Active Cell in Excel

Steps:

• Open Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertRowsfromUser()
iMsg = InputBox("How Many Rows to Insert? (100 Rows maximum)")
numRows = Int(Val(iMsg))
If numRows > 100 Then
numRows = 100
End If
If numRows = 0 Then
GoTo EndInsertRows
End If
Do
Selection.EntireRow.Insert
iCount = iCount + 1
Loop While iCount < numRows
EndInsertRows:
End Sub

• Run the macro.
• In the pop-up window enter the number of rows you want to insert from the active cell.
• Click OK.

This is the output

Method 9 – Using VBA to Include Multiple Rows from an Active Row in Excel

Steps:

• Open Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertRowsfromActiveRow()
Dim iRows As Integer
Dim iCount As Integer
'Select the current row
ActiveCell.EntireRow.Select
On Error GoTo Last
iRows = InputBox("Enter Number of Rows to Insert", "Insert Rows")
'Keep on inserting rows until we reach the input number
For iCount = 1 To iRows
Selection.Insert Shift:=xlToDown, CopyOrigin:=xlFormatFromRightorAbove
Next iCount
Last: Exit Sub
End Sub

• Run the macro.
• In the pop-up window write the number of rows you want to add from your active row.
• Click OK.

3 rows will be inserted from the active row in your dataset.

Read More: Excel Macro to Add Row to Bottom of a Table

Method 10 – Inserting a Row without Formatting in Excel

Steps:

• Open Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertRowWithoutFormatting()
Dim iRow As Long
iRow = 6
With Worksheets("Format")
.Rows(iRow).Insert Shift:=xlShiftDown
.Rows(iRow).ClearFormats
End With
End Sub

• Run the code and a new row without formatting will be inserted.

Method 11 – Utilizing a VBA Macro to Insert a Copied Row in Excel

Steps:

• Open Visual Basic Editor in the Developer tab and Insert a Module.
• Enter the following code.

Sub InsertCopiedRow()
With Worksheets("Copy")
.Rows(6).Copy
.Rows(10).Insert Shift:=xlShiftDown
End With
Application.CutCopyMode = False
End Sub

• Run the code. It will copy row number 6 and paste it in row number 10.

Read More: Macro to Insert Row and Copy Formula in Excel

Download Practice Workbook

Download the free practice Excel workbook here.