You can use the following syntax in VBA to delete rows based on a cell value:
Sub DeleteRowsByValue()
Dim ws As Worksheet
Set ws = ActiveSheet
'clear existing filters
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
'filter range where column 2 in range is equal to "East"
ws.Range("A1:C10").AutoFilter Field:=2, Criteria1:="East"
'delete rows that are visible
Application.DisplayAlerts = False
ws.Range("A2:C10").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
'remove filter
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
End Sub
This particular macro deletes all rows in the range A1:C10 where the value in column B is equal to “East.”
This macro uses the following steps:
- Apply a filter to A1:C10 to only show rows where the value in column B is “East.”
- Then delete all visible cells.
- Then remove the filter.
This has the effect of deleting all rows in the range A1:C10 where the value in column B is equal to “East.”
The following example shows how to use this syntax in practice.
Example: Use VBA to Delete Rows Based on Cell Value
Suppose we have the following dataset that contains information about various basketball players:
Suppose that we would like to delete each row in the dataset where the Conference column is equal to “East.”
We can create the following macro to do so:
Sub DeleteRowsByValue()
Dim ws As Worksheet
Set ws = ActiveSheet
'clear existing filters
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
'filter range where column 2 in range is equal to "East"
ws.Range("A1:C10").AutoFilter Field:=2, Criteria1:="East"
'delete rows that are visible
Application.DisplayAlerts = False
ws.Range("A2:C10").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
'remove filter
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
End Sub
When we run this macro, we receive the following output:
Notice that all rows where the value in the Conference column was “East” have been deleted.
Note: The line Application.DisplayAlerts=False tells VBA not to display the process of deleting the visible rows, which speeds up the process.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Apply Conditional Formatting to Cells
VBA: How to Count Number of Rows in Range
VBA: How to Count Cells with Specific Text