VBA: How to Delete Rows Based on Cell Value

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.