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.

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

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *