How to Use VBA to Delete Rows with Specific Data in Excel (9 Examples)

Dataset Overview

In this tutorial, we’ll explore various examples of deleting rows using Excel VBA. Let’s start by considering the following dataset:

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel

Example 1 – Deleting Entire Rows

• Go to the Developer tab.
• Click on Record Macro and set a name for the macro.
• Click OK.

• Next, click on Macros and choose the macro you just created.
• Step Into the macro.

To enhance understanding, change the color of the rows in your dataset.

• Add the following code to the command module:

Sub Delete_Rows_1()
Rows(10).Delete
End Sub

• Press F5 to run the code.

1.1 Delete Multiple Rows

To delete multiple rows, follow these steps:

• Add the following code to the command module:

Sub Delete_Rows_2()
Rows("8:9").Delete
End Sub

• Press F5 to run the code.

The two rows have been deleted from the dataset.

If your desired rows are not adjacent, enter the following code:

Sub Delete_Rows_X()
Rows(9).Delete
Rows(5).Delete
End Sub

Remember to delete bottom rows first and then work your way up.

1.2 Use the Range Function

Instead of the Rows function, you can use the Range function. Follow these steps:

• Copy and Paste the following code in the command module.

Sub Delete_Rows_3()
Range("b6").EntireRow.Delete
End Sub

• Press F5 to run the code.

Only one row is deleted by using this code. For multiple rows, enter the following code:

Sub Delete_Rows_X()
Range("b6:b8").EntireRow.Delete
End Sub

Example 2 – Eliminating Selected Rows

• Press Alt+F11 to enter the VBA command module.
• Select the colored rows.
• Add the following code:

Sub Delete_Rows_4()
Selection.EntireRow.Delete
End Sub

• Press F5 to run the code.

Selected rows have been eliminated from the dataset.

Example 3 – Excluding Alternate Rows

To exclude every N-th row, follow these steps:

• Hit Alt+F11 to enter the command module.
• Select the entire dataset.
• Enter the below code in the command module.

Sub Delete_Rows_5()
Row_No = Selection.Rows.Count
For x = Row_No To 1 Step -3
Selection.Rows(x).EntireRow.Delete
Next x
End Sub

• Press F5 and run the code.

Example 4 – Deleting Rows with Duplicate Data

• Press Alt+F11 to enter the command module.
• Copy and paste the following code in the command module:

Sub Delete_Rows_6()
Range("B5:D14").RemoveDuplicates Columns:=2
End Sub

• Press F5 to run the code.

Only the 1st occurrences of duplicate data in a specific column will remain in the dataset.

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

Example 5 – Deleting Rows with Empty Cells

• Enter the command module by pressing Alt+F11.
• Enter the code below in the command module:

Sub Delete_Rows_7()
Range("B5:D14").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

• Press F5 to run the code.

The code has eliminated rows containing blank cells.

Example 6 – Removing the Last Row Based on a Column

• Press Alt+F11 to enter the VBA command module
• Copy and paste the following code on the module:

Sub Delete_Rows_8()
Cells(Rows.Count, 2).End(xlUp).EntireRow.Delete
End Sub

• Press F5 to run the code.

We’ve changed the color of the last row to red for better understanding. After running the code, that row will be removed.

Example 7 – Deleting Filtered Visible Rows in Excel

• Apply a filter by pressing Ctrl+Shift+L.
• Filter only the blank cells in Column D.
• To enter the command module, press Alt+F11.
• Copy and paste the following code into the module:

Sub Delete_Rows_9()
Range("B5:B14").SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub

• Press F5 to run the code.

All visible rows meeting the filter criteria will be deleted.

Example 8 – Deleting Rows with a Specific Text in a Single Column

• Select the entire dataset.
• Press Alt+F11 to enter the command module.
• Paste the following code into the module:

Sub Delete_Rows_10()
For x = Selection.Rows.Count To 1 Step -1
If Cells(x, 2).Value = "John" Then
Cells(x, 2).EntireRow.Delete
End If
Next x
End Sub

• Press F5 to run the VBA code.

This code searches for the text John in Column B and removes the corresponding row.

Read More: Excel VBA to Delete Row If Cell Contains Partial Text

Example 9 – Deleting Rows with a Specific Text Anywhere in the Dataset

• Enter the VBA command module by pressing Alt+F11.
• Add the following code to the module:

Sub Delete_Rows_11()
Dim cell As Range
For Each cell In Range("B5:D14")
If cell.Value = "Jose" Then
cell.EntireRow.Delete
End If
Next cell
End Sub

• Press F5 to run the code.

The row containing the text Jose will be removed from the dataset.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Delete Row Using Macro If Cell Contains 0 in Excel
• How to Apply VBA Code to Delete Rows Based on Multiple Cell Value