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.

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

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

Read More: How to Clear Cell Contents Based on Condition in Excel

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.

• 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.

Read More: How to Clear Multiple Cells in Excel

Download Practice Workbook

You can download the practice workbook from here:

Frequently Asked Questions

1. 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.
2. 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.
3. 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.

Related Articles

• How to Clear Recent Documents in Excel
• How to Clear Contents in Excel Without Deleting Formatting
• How to Clear Excel Temp Files
• How to Clear Contents in Excel Without Deleting Formulas

<< Go Back to Clear Contents in Excel | Learn Excel