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.
Read More: How to Clear Cell Contents Based on Condition in Excel
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.
Read More: How to Clear Multiple Cells in Excel
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
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
- Difference Between Delete and Clear Contents in Excel
- How to Clear Recent Documents in Excel
- How to Clear Contents in Excel Without Deleting Formatting
- How to Remove Formatting in Excel Without Removing Contents
- How to Clear Formatting in Excel
- How to Clear Excel Temp Files
- How to Clear Contents in Excel Without Deleting Formulas
<< Go Back to Clear Contents in Excel | Entering and Editing Data in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!