How to Apply VBA to Delete Rows with Specific Data in Excel?

We can delete rows in Excel using the delete command. We need to delete rows based on our needs. But when working with a big dataset, deleting rows kills lots of time. Excel VBA makes it simple to delete rows based on our requirements. In this article, we will discuss how to delete rows with specific data using the Excel VBA.

How to apply VBA to Delete Rows with Specific Data in Excel: 9 Examples

We will apply 9 pieces of Excel VBA codes to delete rows with specific data. We will consider the dataset below for this article.

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

1. Delete the Whole Row Using Excel VBA

In the first example, we will show how to delete an entire row using Excel VBA. To do that, execute the following steps.

Step 1:

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

Step 2:

• Now, click on Macros.
• Choose the macro and Step Into it.

We will change the color of the rows of the dataset before applying the respective VBA for better understanding.

Step 3:

• Provide the following code on the command module.

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

Step 4:

• Then press F5 to run the code.

Delete Multiple Rows:

We can delete a single row by this code. When we need to delete multiple rows follow the next steps.

Step 1:

• Apply the following code on the command module.

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

Step 2:

• Hit F5 to run the code.

Here, two rows have been deleted from the dataset. According to desire, we can delete as many rows from the dataset.

If our desired multiple rows are not adjacent, then we can use the code below.

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

One thing that needs to be kept in mind is that the bottom rows need to be deleted first. Then go towards the top rows.

Use Rows Function Instead:

We can use the Range function instead of the Rows function in the VBA code. Follow the next steps for that.

Step 1:

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

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

Step 2:

• Hit F5 and the VBA code will run.

Only one row is deleted by operating this code. For multiple rows, use the code below.

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

2. Eliminate Selected Rows in Excel

In this section, we will eliminate rows based on our selection of the dataset. Some of the cells of our dataset are blank. We want to eliminate those rows. Now, change the color of those rows. We want to eliminate those rows.

Step 1:

• Press Alt+F11 to enter the VBA command module.
• Select the colored rows and put the following code on the dataset.

Sub Delete_Rows_4()
Selection.EntireRow.Delete
End Sub

Step 2:

• Press F5 to run the code.

Selected rows have been eliminated from the dataset.

3. Exclude Alternate Rows (Or Exclude Every N-th Row)

We can exclude every n-th row using the VBA code. We exclude every 3rd row in this section. Rows will count from the bottom to the top and we change the color of rows accordingly.

Step 1:

• Hit Alt+F11 to enter the command module.
• Select the whole dataset.
• Write the below code on 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

Step 2:

• Press F5 and run the code.

Indicated rows are excluded from the dataset.

4. Delete Rows Based on Duplicate Data on Specific Column

We want to delete rows that contain duplicate data in a specific column in this section. We have duplicate data on the 2nd column in our data range and change the color of those cells. Only keep the 1st occurrence of each duplicate.

Step 1:

• Hit Alt+F11 to enter the command module.
• Copy and paste the following code on the command module.

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

Step 2:

• Hit the F5 button to run the code.

Only the 1st occurrences are present in the dataset.

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

5. Delete Row If Cell Is Empty

We want to eliminate rows that have blank cells. In this section, we will solve this problem.

Step 1:

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

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

Step 2:

• Press F5 to run the code.

We can see that those rows containing blank cells are eliminated.

6. Remove the Last Row Based on Column

We want to remove the last row based on the column. Follow the next steps for that.

Step 1:

• Hit Alt+F11 and enter the 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

Step 2:

• Hit F5 to run the code.

We changed the color of the last row to red for better understanding. After running the code that row has been removed.

7. Delete Filtered Visible Rows in Excel

We want to filter our data and then delete rows based on our requirements. First, apply the filter by pressing Ctrl+Shift+L. Now, the blank cells of Column D only. We will remove those rows by following the below steps.

Step 1:

• To enter the command module press Alt+F11.
• Copy and paste the code below on the module.

Sub Delete_Rows_9()
Range("b5:b14").SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub

Step 2:

• Press F5 and run the code.

See, all the visible rows are deleted.

8. Delete Rows with a Specific Text in a Single Column

We want to delete rows based on a specific text in a single column. We will do this using a VBA code.

Step 1:

• First, select the whole dataset.
• Press Alt+F11 to enter the command module.
• Paste the code below.

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

Step 2:

• Now, press F5 to run the VBA code.

We searched the text “John” in Column B and removed that row.

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

9. Delete Rows with a Specific Text from the Whole Dataset

We want to eliminate rows with specific text found anywhere in the dataset. Apply the VBA code mentioned in the steps below.

Step 1:

• Enter the VBA command module by pressing Alt+F11.
• Put the following code on 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

Step 2:

• Press F5 to run the code.

We can see that the row containing the text “Jose” has been removed from the dataset.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we showed Excel VBA to delete rows with specific data. I hope this will satisfy your needs. Give your suggestions in the comment box.

Related Articles

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