How to Delete Multiple Rows with VBA in Excel (4 Ways)

In this article, I’ll show you how you can delete multiple rows in Excel with VBA. The article will combine deleting multiple rows of a selected range, rows having a specific text, rows maintaining a specific criterion, and rows having blank cells.

Download Practice Workbook

4 Ways to Delete Multiple Rows with VBA in Excel

Here we’ve got a data set with the Names of some books and their Prices of a bookshop called Martin Bookstore.

Today our objective is to delete multiple rows from this data set with VBA in Excel. Let’s explore 4 different ways to delete multiple rows with VBA in Excel.

1. Delete Multiple Rows of a Selected Range

⧪ Step 1:

➤ Press ALT+F11 on your keyboard. The VBA window will open.

⧪ Step 2:

➤ Go to the Insert tab in the VBA window. 

➤ From the options available, select Module.

⧪ Step 3:

➤ A new module window called “Module 1” will open. 

➤ Insert the following VBA code in the module.

Code:

Sub Delete_Seletced_Rows()
Dim rng As Range
Set rng = Selection
For i = 1 To rng.Rows.Count
    rng.Cells(i, 1).EntireRow.Delete
Next i
End Sub

This code produces a Macro called Delete_Selected_Rows.

⧪ Step 4:

➤ Save the workbook as Excel Macro-Enabled Workbook.

⧪ Step 5:

➤ Return to your worksheet by pressing ALT+Q.

➤ Select the rows in your data set that you want to delete.

⧪ Step 6:

➤ Then press ALT+F8 on your keyboard.

➤ A dialogue box called Macro will open. Select Count_Rows (The name of the Macro) and click on Run.

⧪ Step 7:

➤ You will find the selected rows get deleted from your data set.

Read more: How to Use VBA to Delete Empty Rows in Excel

2. Remove Multiple Rows with Specific Text Value

You can also delete multiple rows having a specific text from your data set with VBA in Excel.

The steps are also all same as Method 1 (Step 1-7).

⧪ Just in Step 3, change the VBA code to this::

Code:

Sub Delete_Rows_with_Specific_Text()
Dim rfText As String
rfText = InputBox("Enter the rfText Value: ")
LrfText = LCase(rfText)
For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Rows.Count
        Words = Split(Selection.Cells(j, 1))
        For Each k In Words
            LWord = LCase(k)
            If LrfText = LWord Then
                Selection.Cells(j, 1).EntireRow.Delete
            End If
        Next k
    Next j
Next i
End Sub

This code creates a module called Delete_Rows_with_Specific_Text.

⧪  And in Step 5, before running the code, select the column with the specific text value. Here I have selected range B4:B13 (Name of the Books).

⧪ Then press ALT+F8, select Delete_Rows_with_Specific_Text, and click on Run.

⧪ An Input Box will appear that will ask you to enter the text value that you want to match.

For the sake of this example, I have entered it as “history”.

And you will find all the rows having the specific text (“History” in this case) deleted from your data set.

Read more: VBA Macro to Delete Row if Cell Contains Value in Excel

3. Delete Multiple Rows with Criteria

You can also use a VBA code to delete multiple rows having a specific criterion from your data set.

Let us derive a Macro that will delete the rows with book prices less than or equal to $15.

The steps are also all same as Method 1 (Step 1-7).

⧪ Just in Step 3, change the VBA code to this::

Code:

Sub Deleting_Rows_with_Criteria()
For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Rows.Count
        If Selection.Cells(j, 1) <= 15 Then
            Selection.Cells(j, 1).EntireRow.Delete
        End If
    Next j
Next i
End Sub

Note:

• This code creates a module called Delete_Rows_with_Criteria.
• In line 5, we used “<=15” because this is the criterion we are using. You change it according to your need.

⧪  And in Step 5, before running the code, select the range of cells with the criteria. Here I have selected only column C (C4:C13, Price) because the criterion lies there.

⧪ Then press ALT+F8, select Delete_Rows_with_Criteria, and click on Run.

You will find all the rows with book prices less than or equal to $15 deleted automatically.

4. Delete Multiple Rows with Blank Cells

Finally, you can also use a VBA code to delete multiple rows having blank cells.

Let us consider for a moment that we don’t know the prices of some books, and they are left empty in the data set.

We want to delete the rows with the empty cells.

The steps are all same as Method 1 (Step 1-7).

⧪ Just in Step 3, enter this VBA code in place of the earlier one:

Code:

Sub Deleting_Rows_with_Blank_Cells()
For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Rows.Count
        If Selection.Cells(j, 1) = "" Then
            Selection.Cells(j, 1).EntireRow.Delete
        End If
    Next j
Next i
End Sub

Note:

• This code creates a module called Deleting_Rows_with_Blank_Cells.

⧪  In Step 5, before running the code, select the column with the blank cells. Here I have selected range C4:C13 (Price).

⧪ Then press ALT+F8, select Delete_Rows_with_Blank_Cells, and click on Run.

All the rows with the blank cells in your data set will be deleted automatically.

Conclusion

Using these methods, you can delete multiple rows from your data set with VBA in Excel. Do you have any questions? Feel free to ask us.

Related Articles

• How to Delete Specific Rows in Excel (8 Quick Ways)
• How to Delete Every Other Row In Excel (4 Methods)
• Delete Rows in Excel: 7 Methods
• How to Delete Selected Rows in Excel(8 Approaches)
• How to Delete Multiple Rows in Excel with Condition (3 Ways)
• How to Delete Multiple Rows in Excel (3 Methods)