How to Clear Cells with a Specific Value in Excel (2 Methods)

Dataset Overview

Here we have a dataset containing First Name, Last Name, and Age. There are 3 cells containing Wood that we want to clear.

Method 1 – Using the Find and Replace Feature

Open your Excel workbook containing the dataset with First Name, Last Name, and Age.
Navigate to the Home tab.
In the Editing group, click on Find & Select and choose Replace.
Alternatively, press Ctrl+H to open the Find and Replace dialog.

Opening Find and Replace tool

In the Find What box, enter the value you want to clear (e.g., Wood).
Leave the Replace with box empty.
Click Replace All.

Replacing certain values using Find and Replace tool

Excel will clear all cells containing the specified value, and a notification will display the count of cleared cells.

Method 2 – Using VBA Code

If you’re working with a large worksheet or multiple sheets, consider using VBA (Visual Basic for Applications) to clear cells with specific values quickly.
Go to the Developer tab (if not visible, enable it by right-clicking the ribbon and selecting Customize the Ribbon).
Click on Visual Basic or press Alt + F11 to open the Visual Basic for Applications editor.

Opening VBA window

Insert a new module by clicking on the Insert tab and selecting Module.

Copy and paste the following VBA code into the module:

Option Explicit
Sub Clear_Cells_with_Certain_Value()
Dim xRange As Range
Dim eRange As Range
Set xRange = Worksheets("VBA").Range("B5:C10")
For Each eRange In xRange
    If eRange.Value = "Wood" Then
        eRange.ClearContents
    End If
    Next
End Sub

Press F5 or click the Run button to execute the code.

The specified cells containing the value Wood will be cleared from the specified range.

Showing final result after applying VBA code to clear cells with certain value in Excel

Download Practice Workbook

You can download the practice workbook from here:

Clearing Cells with Certain Value.xlsm

Frequently Asked Questions

Can I clear cells based on a condition in Excel?
- Yes, you can use conditional formatting to highlight cells that meet specific criteria, and then manually clear or delete those highlighted cells.
Can I use a formula to clear cells with certain values?
- No, Excel formulas are primarily designed for calculations and data manipulation, not for directly clearing or deleting cells. However, you can use formulas to mark cells for deletion and then perform the deletion manually.
How do I clear cells with certain values across multiple sheets in Excel?
- To clear cells with specific values across multiple sheets, you can use the Find and Replace feature individually on each sheet or use VBA for a more automated approach.