Table of Contents
Using VBA, you can delete rows based on cell value by looping through all the rows in the spreadsheet and testing the cell values. If the cell value matches the criteria you specify, you can then delete the row. To do this, you’ll need to create a macro with a For Loop, an If statement, and the Delete method. This can be used to delete rows based on the value of a single cell, or to delete multiple rows with different criteria.
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.