The sample dataset showcases Salesman, Product, and Net Sales of a company.
Method 1 – Filtering the Visible Rows and Deleting them with VBA in Excel
In the dataset below, product AC is our single criterion. Only the rows with AC will be visible.
1.1 Single Criterion
STEPS:
- Go the Developer tab and select Visual Basic.
- In the new window, in Insert, select Module.
- Another window will be displayed.
- Enter the code in the module window.
Sub FilterDeleteVisible()
Dim range As range
Set range = Selection
range.AutoFilter Field:=2, Criteria1:="AC"
range.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub
- Close Visual Basic.
- Select the range including the header.
- In the Developer tab, select Macros.
- In Macro name, select FilterDeleteVisible .
- Click Run and you’ll only see the rows with the product Cable.
1.2 Multiple Criteria
STEPS:
- Click Visual Basic in the Developer tab.
- In the new window, choose Insert and select Module.
- Enter the following code in the module window.
Sub MultipleCriteria()
Dim rows As range
Dim range As range
Set range = Selection
range.AutoFilter Field:=2, Criteria1:="AC"
range.AutoFilter Field:=3, Criteria1:=">10000"
For Each rows In range.rows
Next
range.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub
- Close Visual Basic.
- Select the range.
- In the Developer tab, select Macros.
- In the dialog box, select MultipleCriteria in Macro name.
- Click Run.
This is the output.
Read More: How to Use Macro to Delete Rows Based on Criteria in Excel
Method 2 -Using Excel VBA to Delete Hidden Filtered Rows
In the dataset below, two criteria will be applied: product AC and Net Sales Greater than $10,000. You want to delete the hidden rows which don’t meet the criteria.
STEPS:
- Select Visual Basic in the Developer tab.
- In the new window, choose Insert and select Module.
- Enter the following code.
Sub FilterDeleteHidden()
Dim uni As range
Dim rows As range
Dim range As range
Set range = Selection
range.AutoFilter Field:=2, Criteria1:="AC"
range.AutoFilter Field:=3, Criteria1:=">10000"
For Each rows In range.rows
If rows.Hidden Then
If Not uni Is Nothing Then
Set uni = union(uni, rows)
Else
Set uni = rows
End If
End If
Next
uni.Delete
ActiveSheet.AutoFilterMode = False
End Sub
- Close Visual Basic.
- Select the range.
- Select Macros in the Developer tab.
- In the dialog box, select FilterDeleteHidden in Macro name.
- Click Run.
- Click OK.
- Hidden rows will be deleted and only visible rows will be displayed.
Read More: Excel VBA to Delete Row If Cell Contains Partial Text
Download Practice Workbook
Download the following workbook.
Related Articles
- How to Apply VBA Code to Delete Rows Based on Multiple Cell Value
- VBA to Delete Every Other Row in Excel
- Excel VBA: Delete Row If Cell Is Blank
- How to Delete Row Using Macro If Cell Contains 0 in Excel
- How to Apply VBA to Delete Rows with Specific Data in Excel
- How to Use VBA to Delete Empty Rows in Excel
Thanks. I have a workbook in which many thousands of rows needed to be deleted at a time. This was very slow, tens of seconds, using this method. I had an idea and found it to be very fast. Instead of deleting rows, I clear contents and then resort the data thus removing all the blank rows created. It is very fast and where appropriate, perhaps a good alternative to have in the toolbox