Using a VBA Macro to Insert Rows in Excel Based on Criteria

Method 1 – Using a VBA Macro to Insert Rows Based on the Cell Text Value in Excel

To insert a new row based on the specific text value “Insert Above” in the following dataset: dataset of macro to insert row in excel based on criteria of cell text value

Steps:

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

Click 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 just click the small Play icon to run the macro.

A new row is inserted above “Insert Above”.

Method 2 – Using a VBA Macro to Insert a Row Based on the Cell Numeric Value in Excel

Insert a new row above the cell containing 99:

Steps:

Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
Click Insert -> 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

Your code is now ready to run.

Run the code.

This is the output.

Read More: Excel VBA: Insert Row with Values

Method 3 – Using a Macro to Insert Multiple Rows Based on a Condition in Excel

To insert a row with specific values:

Steps:

Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
Click Insert -> Module.
Enter the following code.

Sub InsertMultipleRows()
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.
The selected values are inserted.

Method 4 – Using a VBA Macro to Insert a Row after Each Record in Excel

Steps:

Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
Click Insert -> 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

Run the code.

This is the output.

Download Practice Template

Download the free practice Excel template.

Macro to Insert Row in Excel Based on Criteria.xlsm

Get FREE Advanced Excel Exercises with Solutions!

Sub InsertRows() Dim Qty As Integer Dim sourceSheet As Worksheet Dim destinationSheet As Worksheet Dim lastRow As Long Dim pasteRange As Range Set sourceSheet = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the actual name of the source sheet Set destinationSheet = ThisWorkbook.Worksheets("Sheet2") ' Replace "Sheet2" with the actual name of the destination sheet Qty = sourceSheet.Range("E3").Value lastRow = destinationSheet.Cells(destinationSheet.Rows.Count, "B").End(xlUp).Row Set pasteRange = destinationSheet.Cells(lastRow + 1, "B").Resize(Qty, 2) pasteRange.Insert Shift:=xlDown destinationSheet.Cells(lastRow + 1, "B").Resize(Qty, 2).Value = sourceSheet.Range("C3:D3").Value Application.CutCopyMode = False destinationSheet.Select ' Optional: Select the destination sheet after inserting rows End Sub

2 Comments

Prasanth May 27, 2023 at 11:34 PM

Hello Sanjida. In the criteria3 method, of inserting rows based on a pre-defined value in a column, i want to insert the rows in a seperate sheet. how to do that ? can you please tell me
- Reply
  Rubayed Razib Suprov May 28, 2023 at 5:15 PM
  Greetings Prasanth,
  Thanks a lot for the question you asked. Below I provide a code using which you can add rows in another worksheet. When you run the code, then you will see that the values stored in the C3:D3 insert in another worksheet. The values are now repeated according to the value mentioned in the cell E3
```
Sub InsertRows()
Dim Qty As Integer
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim lastRow As Long
Dim pasteRange As Range

Set sourceSheet = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the actual name of the source sheet
Set destinationSheet = ThisWorkbook.Worksheets("Sheet2") ' Replace "Sheet2" with the actual name of the destination sheet

Qty = sourceSheet.Range("E3").Value

lastRow = destinationSheet.Cells(destinationSheet.Rows.Count, "B").End(xlUp).Row

Set pasteRange = destinationSheet.Cells(lastRow + 1, "B").Resize(Qty, 2)

pasteRange.Insert Shift:=xlDown

destinationSheet.Cells(lastRow + 1, "B").Resize(Qty, 2).Value = sourceSheet.Range("C3:D3").Value

Application.CutCopyMode = False
destinationSheet.Select ' Optional: Select the destination sheet after inserting rows
End Sub
```

Using a VBA Macro to Insert Rows in Excel Based on Criteria – 4 Methods

Method 1 – Using a VBA Macro to Insert Rows Based on the Cell Text Value in Excel

Method 2 – Using a VBA Macro to Insert a Row Based on the Cell Numeric Value in Excel

Method 3 – Using a Macro to Insert Multiple Rows Based on a Condition in Excel

Method 4 – Using a VBA Macro to Insert a Row after Each Record in Excel

Related Article

Leave a reply Cancel reply