VBA: How to Clear Contents if Cell Contains Specific Value

You can use the following basic syntax to clear the contents of each cell in an Excel worksheet that is equal to a specific value:

Sub ClearContentsIfContains()

Dim cell, rng As Range
Set rng = Range("A2:A11")

For Each cell In rng
    If cell.Value = "Mavs" Then
    cell.ClearContents
    Else
    End If
Next cell

End Sub

This particular macro will clear the contents of each cell in the range A2:A11 that is equal to “Mavs”.

The following example shows how to use this syntax in practice.

Example: Use VBA to Clear Contents if Cell Contains Specific Value

Suppose we have an Excel sheet that contains information about various basketball players:

Suppose we would like to clear the contents of each cell in the Team column that is equal to “Mavs”.

We can create the following macro to do so:

Sub ClearContentsIfContains()

Dim cell, rng As Range
Set rng = Range("A2:A11")

For Each cell In rng
    If cell.Value = "Mavs" Then
    cell.ClearContents
    Else
    End If
Next cell

End Sub

Once we run this macro, all cells with a value of “Mavs” in the Team column will be cleared:

If you would instead like to clear all cells in each row that has a value of “Mavs” in the Team column then you can use the following syntax:

Sub ClearContentsIfContains()

Dim cell, rng As Range
Set rng = Range("A2:A11")

For Each cell In rng
    If cell.Value = "Mavs" Then
    cell.EntireRow.ClearContents
    Else
    End If
Next cell

End Sub

Once we run this macro, we receive the following output:

Notice that all of the cells in each row that contained “Mavs” in the Team column have been cleared.

Note: You can find the complete documentation for the ClearContents method in VBA here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Find Last Used Row
VBA: How to Find Last Used Column
VBA: How to Unhide All Sheets