How to Clear Cells with Certain Value in Excel (2 Ways)

Clearing cells with a certain value means finding a specific value and erasing the cell content. While working in a large data set, clearing certain values by going through the cells one by one is time-consuming and tedious. You can face it for the same values or multiple types of values.

In this Excel tutorial, we will clear cells with a certain value in Excel using the Find and Replace feature and VBA code.

Here we have a dataset containing First Name, Last Name, and Age. There are 3 cells containing “Wood”. We cleared those cells by using the Find & Replace feature.

Here are the 2 ways to clear cells with certain values in Excel:

Using Find and Replace Feature

The Find & Replace feature of Excel can look for a certain value after giving input and then will either replace them one by one or all of them at once. So the drawback of using it is- it can’t handle multiple type values at a time. Now, let’s clear cells containing “Wood” from the following dataset using this feature.

To clear cells with certain values using the Find and Replace feature, follow the steps below:

Go to the Home tab > Editing group > Find & Select > Replace.
Or, press Ctrl+H to open the Find and Replace box.
In the ‘Find What‘ box, insert the value that you want to clear.
Keep the ‘Replace with‘ box empty.
Click on ‘Replace All‘.

All the cells containing the certain value will be cleared and a notification box will show the count.

Using VBA Code

When you are working with a large worksheet and several sheets, you can use VBA to clear cells with certain values rapidly.

Here are the steps to clear certain values using VBA:

Go to the Developer tab > Visual Basic. Or, you can press Alt + F11.
Click on Insert tab > Module to insert a new module.

Copy the following code and paste it 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 the Run button to execute the code.

After running the code, the cells containing the specified value will be cleared from the specified range.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Clearing Cells with Certain Value.xlsm

Conclusion

The Find and Replace tool will particularly help you to clear values one by one and the VBA method will be effective for large and multiple sheets. Overall, the two straightforward methods will enable you to maintain accuracy and enhance your data management efficiency, ultimately contributing to a more organized and effective workflow. Applying this skills will undoubtedly save time and streamline your Excel experience.
Please share any further queries or recommendations with us in the comments section below.

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 (Visual Basic for Applications) for a more automated approach.

Related Articles

<< Go Back to Clear Contents in Excel | Entering and Editing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!