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

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.

Download Practice Workbook

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

9 Examples to Delete Rows with Specific Data Using Excel VBA

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

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 keep in mind is that bottom rows need to delete 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.

Read More: How to Delete Selected Rows in Excel(8 Approaches)

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.

Read More: How to Delete Alternate Rows in Excel (5 Quick Ways)

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 Remove Duplicate Rows Based on One Column Using Excel VBA

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 rin the code.

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

Read More: How to Delete Every Other Row In Excel (4 Methods)

Similar Readings:

• How to Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)
• Delete Rows in Excel That Go on Forever (4 Easy Ways)
• VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)
• How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)
• How to Delete Infinite Rows in Excel (5 Easy Ways)

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 into red for better understanding. After running the code that row has been removed.

Read More: How to Delete Specific Rows in Excel (8 Quick Ways)

7. Delete Filtered Visible Rows in Excel

We want to filter our data and then delete rows basis on our requirements. First, apply filter pressing Ctrl+Shift+L. Now, visible 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.

Read More: How to Delete Filtered Rows in Excel (5 Methods)

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 search the text “John” in Column B and removed that row.

Read More: How to Delete Rows in Excel with Specific Text (3 Methods)

9. Delete Rows with a Specific Text from 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 row containing the text “Jose” has been removed from the dataset.

Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)

Conclusion

In this article, we showed Excel VBA to delete rows with specific data. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Related Articles

• Excel Delete Rows in a Range with VBA (3 Easy Ways)
• Excel VBA Code to Delete Rows Based on Multiple Cell Value (3 Criteria)
• Macro to Delete Row in Excel If Cell is Blank
• Formula to Remove Blank Rows in Excel (5 Examples)
• How to Delete Unused Rows in Excel (8 Easy Ways)