Delete Multiple Rows in Excel with VBA (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.

Data Set to Delete Multiple Rows with VBA in Excel

Today our objective is to delete multiple rows from this data set with VBA in Excel.


1. Delete Multiple Rows of a Selected Range with VBA in Excel

Step 1:

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

VBA Window to Delete Multiple Rows with VBA in Excel

Step 2:

Go to the Insert tab in the VBA window. 

From the options available, select Module.

Inserting Module to Delete Multiple Rows with VBA in Excel

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

Notes:

  • This code produces a Macro called Delete_Selected_Rows.

VBA Code to Delete Multiple Rows with VBA in Excel

Step 4:

Save the workbook as Excel Macro-Enabled Workbook.

Step 5:

➤ Return to your worksheet.

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.

Running Macro to Delete Multiple Rows with VBA in Excel

Step 7:

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

VBA Code Used to Delete Multiple Rows in Excel

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


2. Remove Multiple Rows Having a Specific Text Value with VBA in Excel

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 Text As String
Text = InputBox("Enter the Text Value: ")
LText = LCase(Text)
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 LText = LWord Then
                Selection.Cells(j, 1).EntireRow.Delete
            End If
        Next k
    Next j
Next i
End Sub

Note:

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

Selecting Cells to Delete Multiple Rows with VBA

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

Running Macro to Delete Multiple Rows with VBA

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

Output to Delete Multiple Rows with VBA

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


3. Delete Multiple Rows with Criteria with VBA in Excel

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

VBA Code to Delete Multiple Rows with VBA in Excel

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

Running Macro to Delete Multiple Rows with VBA in Excel

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

Output to Delete Multiple Rows with VBA in Excel


4. Delete Multiple Rows with Blank Cells with VBA in Excel

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

VBA Code to Delete Multiple Rows with VBA in Excel

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

Selecting Cells to Delete Multiple Rows with VBA in Excel

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

Output to Delete Multiple Rows with VBA in Excel


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

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo