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

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

How to Delete Multiple Rows with VBA in Excel: 4 Ways

1. Delete Multiple Rows of a Selected Range

2. Remove Multiple Rows with Specific Text Value

3. Delete Multiple Rows with Criteria

4. Delete Multiple Rows with Blank Cells

Conclusion

How to Delete Multiple Rows with VBA in Excel: 4 Ways

Here we’ve got a data set with the Names of some books and the 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 an 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.

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 (Steps 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.

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 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.

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 (Steps 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 the same as Method 1 (Steps 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: