The article will show you how to clear cell contents based on condition in Excel. It’s easier to clear the cell contents at once based on conditions than to clear them one by one. It is also a time-saving process. When an Excel dataset contains similar kinds of data, and after the application of those data, they kind of become irrelevant. So we need to clear the cells that contain this irrelevant data and use the updated data. In this article, I’ll show you how to apply conditions to clear cell contents in Excel.
How to Clear Cell Contents Based on Condition in Excel: 7 Ways
In the dataset, you will see information about some sports cars. The information includes their manufacturer, model name, price, and their maximum speeds. You can notice that some of the cars have the same manufacturers or same price or same maximum velocity. We will show you how to clear some contents from this dataset based on different conditions.
1. Clearing Cell Contents Based on Position
In this section, I’ll show you how to apply Microsoft Visual Basic for Applications (VBA) to clear the cell contents based on their position in the Excel sheet. Let’s go through the process below for a better understanding.
- First, go to the Developer Tab and then select Visual Basic.
- After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.
- Next, type the following code in the Module.
Sub ClearMidCells()
Dim mn_worksheet As Worksheet
Dim mn_find_value As Range
Application.ScreenUpdating = False
For Each mn_worksheet In Worksheets
    mn_worksheet.Select
    If mn_worksheet.Name = "mid cells" Then
        For Each mn_cell_value In Range("B7:B10")
            Set mn_find_value = Range("E7:E10").Find(mn_cell_value.Value)
            If mn_find_value Is Nothing Then
                Range(Cells(mn_cell_value.Row, "C"), Cells(mn_cell_value.Row, "D")).ClearContents
            End If
        Next mn_cell_value
    End If
Next mn_worksheet
Application.ScreenUpdating = True
End Sub
Code Explanation
- First, we declared the Sub Procedure and some necessary variables.
- After that, we set the ScreenUpdating Application to False.
- Next, we used a For Next Loop and If Statement to define the position of the cells whose contents we will clear later. The ClearContents method is used to clear the cell contents from the range C7:D10.
- Thereafter, we set the ScreenUpdating Application to True.
- Finally, we run the code.
- Thereafter, go back to your sheet and run the Macro named ClearMidCells.
- After that, the cell contents of the range C7:D10 will be gone from the dataset as we defined this range in the code.
Thus, you can clear the cell contents based on their position in the dataset.
Read More: How to Clear Multiple Cells in Excel
2. Clearing Cell Contents Based on Another Cell’s Value
We can also use VBA to clear the cell contents changing the value of another cell. Let’s have a look at the process below.
Steps:
- First, right click on the sheet name of your Excel workbook and click on View Code to open it.
- After that, type the following code in the sheet module. Keep in mind that you should activate the Worksheet of the module.
Private Sub Worksheet_Change(ByVal mnTarget As Range)
If Not Intersect(mnTarget, Range("A2")) Is Nothing Then
Range("C7:C10").ClearContents
End If
End Sub
The Macro will clear the cell contents from the C7:C10 range if the value of A2 is changed.
- Now go back to your sheet and insert any value in cell A2. It can be a single character too.
- After you press the ENTER button, the cell contents in C7:C10 will immediately disappear.
Thus you can clear cell contents of your dataset based on changing the value of another cell.
3. Clearing Particular Value from Cell
Suppose, you don’t want the car information that has a price range over 250,000 bucks. You can clear the cell contents based on this price range. Let’s go through the process below for a better understanding.
Steps:
- First, follow the steps in Method 1 to open a VBA Module.
- After that, type the following code in the VBA Module.
Sub ClearParticularCells()
    Dim mn_last_row As Long
    Dim k As Long
    Application.ScreenUpdating = False
    mn_last_row = Cells(Rows.Count, "D").End(xlUp).Row
    For k = 5 To mn_last_row
        If Cells(k, "D").Value > 250000 Then Range(Cells(k, "B"), Cells(k, "E")).ClearContents
    Next k
    Application.ScreenUpdating = True
End Sub
After executing the code, you will see the data of the cars that have prices of more than 250,000 dollars are cleared. The Macro will test the values in column D whether they are greater than 250000 or not. If so, it will clear the contents from columns B to E of the corresponding row.
- Next, go back to your sheet and run the Macro named ClearParticulaCells as it is the name of our current Macro.
- Thereafter, you won’t see the cell contents that contained the car information having a price range over 250000 bucks.
Thus, you can effectively clear particular cell contents based on the range of value.
4. Clearing Cell Contents Based on Color
You can also clear the cell contents based on their background color. Let’s see the modified dataset with the background color first.
Please continue to the description below.
Steps:
- First, follow the steps in Method 1 to open a VBA Module.
- After that, type the following code in the VBA Module.
Sub ClearByColor()
Dim mnCell_Color As Long
Dim i, j As Integer
For i = 5 To 14
For j = 5 To 14
mnCell_Color = Cells(i, j).Interior.Color
Select Case mnCell_Color
Case 13998939
Cells(i, j).ClearContents
Case 12566463
Cells(i, j).ClearContents
Case 5296274
Cells(i, j).ClearContents
End Select
Next j
Next i
End Sub
In the dataset, you can see that the 5th column (Column E) of the dataset contains some cells with background colors. The objective here is to clear those cell contents by the Macro.
Code Explanation
- First, we declared the Sub Procedure and some necessary variables.
- After that, we used a nested For Loop to run the Macro through column E and check whether any color is present in any of the cells of that column.
- We also set the color code for Macro to find the cells with background colors and clear them. We used the ClearContents method to clear the cells.
- Finally, we run the code.
- Thereafter, go back to your worksheet and run the Macro named ClearByColor as it is the current Macro.
- After that, you will see the cells containing background colors are cleared.
Thus you can clear cell contents based on their background color.
Note:
It’s impossible to know all the color codes in Excel. That’s why I’m giving you a VBA code here so that you can easily know about the color code of any background color.
Sub FindColorNumber()
MsgBox Selection.Interior.Color
End SubTo run this code properly, select a cell that contains background color and run the Macro. The Macro will return the color code by a Message Box.
Read More:Â How to Clear Formatting in Excel
5. Clearing Excel Cell Contents Based on Value Condition
If you completely clear the entire row which has certain data as cell contents, you can use the method of this section. Suppose, you don’t want to buy a car which has a price of more than 250 thousand bucks. So you want them out of your Excel sheet. Let’s see the process.
Steps:
- First, follow the steps in Method 1 to open a VBA Module.
- After that, type the following code in the VBA Module.
Sub ClearContentsByRow()
For x = 1 To Selection.Rows.Count
    For y = 1 To Selection.Rows.Count
        If Selection.Cells(y, 3) > 250000 Then
            Rows(y + 4).EntireRow.Delete
        End If
    Next y
Next x
End Sub
Here, We’ve created a ClearContentsByRow(); x and y are variables to count rows. We used a nested For loop to detect a cell of column no 3 if it is greater than 250000 for that we used the IF statement. Then used the EntireRow property to select that row and deleted the row using the Delete method.
For example, when y = 1, it will select the cell which is in row number 1 and column number 3 of the table you select. In this case, it will take the D5 cell to consider. Then it will check all the cells in column 3 and clear the cell contents of the rows which contain more than 250000 dollars.
- Save the code and go back to your worksheet.
- Now select the range B5:E14.
- After that, run the Macro named ClearContentsByRow as it is the current Macro.
- Thereafter, you will see the cells in the rows containing cars which have prices less than or equal to 250 thousand dollars only. Rows that had cars with prices more than this amount are deleted now.
Thus you can clear the entire row based on the cell contents.
Read More: How to Clear Cells with Certain Value in Excel
6. Using Excel IF Function to Clear Cell Contents
We can check if a condition is desired or not using the IF function. Suppose, you want to buy a car that has a maximum speed above or equal to 230 miles per hour and you don’t want other cars on the screen. So you need to clear those cell contents. You can clear them by following the steps described below.
Steps:
- First, make a column to store the speed condition and type the following formula in cell F5.
=IF(E5>=230,"Yes","No")
Here, the IF function checks the condition in which the value of the E5 cell is greater than or equal to 230 mph or not. If the condition is met, it returns Yes. Otherwise, it returns No.
- After that, hit ENTER and you will see the output in it.
- Later, use the Fill Handle to AutoFill the rest of the cells.
- Cars that have a maximum speed above or equal to 230 mph are marked Yes, the others are marked as No.
Here, I will apply a Filter to delete multiple rows.
- Now, select Range B4:F14.
- Next, choose Home >> Sort and Filter. You may also use CTRL + SHIFT + L shortcut key to apply the command.
- Open the filter by clicking on the drop down icon and uncheck Yes, and then press ENTER or click OK.
- Thereafter, you will see the information on cars that have a maximum speed of less than 230 mph. Select the appeared data and press CTRL+- and a warning message will show up. Just click OK.
After that, all the selected rows will be deleted.
- Now to see the hidden rows, you need to toggle the Filter You can press CTRL+SHIFT+L or choose Data >> deselect Filter.
Now the cells that contained information of cars with a maximum speed of less than 230 mph are cleared.
7. Applying Find and Replace Feature to Clear Cell Contents
Another way to clear the cell contents based on condition is to apply the Find and Replace feature of Excel. Suppose you want to clear the Manufacturer Company Porsche out of your dataset. Just stick to the following section of this article to see how we can do this by the Find and Replace feature.
Steps:
- First, select the Company Name column and go to Home >> Find & Select >> Replace.
- After that, the Find and Replace window will show up. Type Porsche in the Find what section and insert a Space just by pressing the SPACE button in the Replace with:Â box.
- Next, click on Replace All.
- This operation will clear the cell contents that contained the manufacturer name Porsche.
Thus you can clear cell contents by using the Find & Replace feature of Excel based on a condition.
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
In the end, we can pull the bottom line by considering that you will learn some easy and efficient ways of how to clear cell contents based on a condition in Excel. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
