VBA Macro to Insert Row in Excel Based on Criteria (4 Methods)

Sometimes we need to modify our large dataset in Excel by inserting rows based on certain conditions. Implementing VBA macro is the most effective, quickest and safest method to run any operation in Excel. In this article, we will show you how to insert row in Excel based on different criteria utilizing the VBA macro.

Excel VBA Macro to Insert Row Based on Criteria: 4 Methods

In this section, we will discuss how to insert row in Excel based on cell value and user predefined value by using VBA macro.

Criteria 1: VBA Macro to Insert Row Based on Cell Text Value in Excel

Consider the following dataset where we will be inserting a new row based on a specific text value of a cell. And we set the value as “Insert Above”. So whenever we run the macro, there will be a newly inserted row right above the row containing that specific cell value.

The steps to insert a new row based on the cell specific text value with VBA macro are given below.

Steps:

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

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

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

Your code is now ready to run.

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

You will notice that there will be a new inserted row right above the cell containing “Insert Above” text value.

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

Criteria 2: VBA Macro to Embed Row Based on Cell Numeric Value in Excel

We have learned how to insert a row based on a specific cell text value. But in this section, we will learn how to insert a row based on a specific numeric value in Excel.

Consider the following dataset where we will be inserting a new row right above the cell containing 99.

The steps to insert a new row based on the cell specific numeric value with VBA macro are given below.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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.

• In the same way as before, Run the code and you will notice that there will be a newly inserted row right above the cell containing 99.

Read More: Excel VBA: Insert Row with Values 

Criteria 3: Macro to Insert Multiple Rows Based on Predefined Condition in Excel

Look at the following example, where we already have a dataset of multiple rows and need to insert a new record as shown in the top table.

What we are going to do with this dataset is, we will insert the amount of rows that is predefined by the user. That means, we will insert “3 Quantity” of rows of “Name Root” and “Score 100” (from the top table) in our actual dataset (bottom table).

The steps on how to do that are given below.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

• In the same way as before, Run the code and you will notice that the new record (Cell C3, D3) is inserted with the number of the Quantity predefined by the user (Cell E3).

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

Criteria 4: VBA Macro to Enter Single Row after Each Record in Excel

What if you want to have some spaces by inserting rows after each record in your large dataset?

With the following dataset as an example, we will show you how to do that using VBA in Excel.

The steps to insert a single row after each record in Excel are given below.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

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

Your code is now ready to run.

-> You can change Cells(lRow – 1, “B”) to whatever value you want to trigger your row insert and, of course, what column this is being applied to.

• In the same way as before, Run the code and you will notice that there are new inserted single rows after each old record.

Download Practice Template

You can download the free practice Excel template from here.

Conclusion

This article showed you how to insert single row and multiple rows in Excel based on different criteria by using VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Related Article

• How to Use VBA to Insert Row in Excel 
• Macro to Insert Row and Copy Formula in Excel