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

Get FREE Advanced Excel Exercises with Solutions!

Users may need to clear cells with a certain value while working in Excel. This can either be a single cell or multiple cells with the same cell value. While working in a large data set, clearing certain values by going through the cells one by one is time-consuming and tedious. So, in this article, we will show you how to clear cells with a certain value in Excel.

Table of Contents hide

Download Practice Workbook

2 Handy Ways to Clear Cells with Certain Value in Excel

1. Using Find and Replace Feature to Clear Cells with Certain Value

2. Applying VBA Code to Clear Cells with Certain Value in Excel

Conclusion

Download Practice Workbook

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

Clearing Cells with Certain Value.xlsm

2 Handy Ways to Clear Cells with Certain Value in Excel

In this article, you will see two easy and handy ways to clear cells with a certain value in Excel. In our first method, we will use the Find and Replace feature of Excel to clear cells. As for the second procedure, we will apply a VBA code to do the same.

For a better understanding of our procedure, we will use the following data set. In this data set, we have the first name, last name, and age of some random people.

1. Using Find and Replace Feature to Clear Cells with Certain Value

In our first procedure, we will use the Find and Replace feature of Excel. This feature will look for a certain value after giving input and then will either replace them one by one or all of them at once. The steps for using this handy feature are given below.

Step 1:

First of all, after preparing the data set go to the Home tab of the ribbon.
Then, from the Editing group choose Find & Select.

Step 2:

Secondly, select the Replace command from the drop-down.
By selecting this command, you will bring up the Find & Replace dialogue box.
Alternatively, you can just press Ctrl+H to do the same.

Step 3:

Then, we will give some input into the dialogue box to achieve our goal.
Firstly, in the Find what type box, insert the name or number or any characters that you want to clear.
Furthermore, the input must be anything from your data set.
In our example, we want to clear the cells that have the name “Wood” in them.
Then, click some options for the matching case and cell content.
Lastly, click on Replace All.

Step 4:

Finally, after completing the previous step, you will see that all the cells containing the value “Wood” will be cleared.
Lastly, close the dialogue box.

2. Applying VBA Code to Clear Cells with Certain Value in Excel

As for our second method, we will apply a VBA code to clear cells. In the code, we will set the specific cell value and give some commands to clear cells containing the exact value. We will describe the entire procedure in the following steps.

Step 1:

Firstly, go to the Developer tab of the ribbon.
Then from the Code group choose Visual Basic.

Step 2:

Secondly, you will see the Visual Basic window.
Then, from the Insert tab, select Module.

Step 3:

Thirdly, copy the following code and paste it into the module.
Here, we want to clear the cells that contain the value “Wood” through this code.

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

VBA Breakdown

Firstly, we are calling the Sub procedure Clear_Cells_with_Certain_Value.

Sub Clear_Cells_with_Certain_Value()

Secondly, we define the variable types.

Dim xRange As Range
Dim eRange As Range

Thirdly, we set the specific data range of the worksheet for clearing cell value.

Set xRange = Worksheets("VBA").Range("B5:C10")

Then, we go through all cells from the data range using the For Each Next Loop.
Here, we have used the eRange.ClearContents command to clear only the cell value.
If you want to delete the cell format as well, then use the eRange.Clear command instead of the previous one.

For Each eRange In xRange
    If eRange.Value = "Wood" Then
        eRange.ClearContents
    End If
    Next
End Sub

Step 4:

Fourthly, keep the cursor in the module and press F5 or the Run button to execute the code.
After that, save the code to use it for further operations.

Step 5:

Finally, after running the code, the cells containing “Wood” will be cleared.

Conclusion

That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to clear cells with a certain value in Excel by using any of the above-mentioned methods. Please share any further queries or recommendations with us in the comments section below.

The Exceldemy team is always concerned about your preferences. Moreover, you need to keep in mind that the submitted comments need to be approved. Therefore, after commenting, be patient and we will reply to your queries as soon as possible.