How to Clear Cell Content Based on a Condition in Excel

This dataset contains similar data and an overview of the methods applied.

The sample dataset showcases information about sports cars. The information includes manufacturer, model, price and maximum speed.

Method 1 – Cearing Cell Content Based on Position

Steps:

Go to the Developer Tab and select Visual Basic.

In the VBA editor, select Insert >> Module to open a VBA Module.

Enter 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 Breakdown

The Sub Procedure and necessary variables are declared.
The ScreenUpdating Application is set to False.
A For Next Loop and an If Statement are used to define the position of the cells whose content we be will cleared. The ClearContents method is used to clear cell content in the range C7:D10.
The ScreenUpdating Application is set to True.
The code is run.

Go back to your sheet and run the Macro (ClearMidCells).

Cell content in C7:D10 will be cleared.

Method 2 – Clearing Cell Content Based on Another Cell’s Value

Steps:

Right click the sheet name and click View Code to open it.

Enter the following code in the module. (The Worksheet containing the module must be active).

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 cell content in the C7:C10 range if the value of A2 is changed.

Go back to your sheet and insert any value in A2.
Press ENTER an cell content in C7:C10 will be cleared.

Method 3 – Clearing a Particular Value from a Cell

Steps:

Follow the steps in Method 1 to open a VBA Module.
Enter 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 running the code, data of cars whose prices are greater than 250,000 dollars will be cleared. The Macro will test if values in column D are greater than 250000. If so, it will clear the content from columns B to E in the corresponding row.

Go back to your sheet and run the Macro (ClearParticulaCells).

No cell content for prices over 250000 will be displayed.

Method 4 – Clearing Cell Content Based on Color

This is the modified dataset.

Steps:

Follow the steps in Method 1 to open a VBA Module.
Enter 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

Code Breakdown

The Sub Procedure and some necessary variables are declared.
A nested For Loop is used to run the Macro through column E and check whether color is present in any of the cells.
The color code for Macro is set to find cells with background color and clear them. The ClearContents method clears the cells.
The code is run.

Go back to your worksheet and run the Macro (ClearByColor).

Cells containing background colors are cleared.

Note:

It’s impossible to know all the color codes in Excel. This is the VBA color code:

Sub FindColorNumber()
MsgBox Selection.Interior.Color
End Sub

To run this code, select a cell with background color and run the Macro.

Method 5 – Clearing Excel Cell Content Based on the Value Condition

Steps:

Follow the steps in Method 1 to open a VBA Module.
Enter 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

The ClearContentsByRow(); x and y are variables count rows. The nested For loop and the IF statement detect if a cell of column 3 is greater than 250000. The EntireRow property selects that row and deletes it 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. Here, it will take D5 and will check all the cells in column 3 and clear the cell content of the rows containing more than 250000 dollars.

Save the code and go back to your worksheet.
Select the range B5:E14.
Run the Macro (ClearContentsByRow).

This is the output.

Method 6 – Using the IF Function to Clear Cell Content

You only want to see cars whose maximum speed is equal to or higher than 230 miles per hour.

Insert a column to store the speed condition and enter the following formula in F5.

=IF(E5>=230,"Yes","No")

The IF function checks the condition in which the value of E5 is greater than or equal to 230 mph. If the condition is met, it returns Yes. Otherwise, it returns No.

Press ENTER and you will see the output.

Use the Fill Handle to AutoFill the rest of the cells.

Use a Filter to delete multiple rows.

Select Range B4:F14.
Choose Home >> Sort and Filter. You may also press CTRL + SHIFT + L.

Open the filter by clicking on the drop down icon and uncheck Yes.
Press ENTER or click OK.

You will see the information on cars that have a maximum speed of less than 230 mph.
Select the data and press CTRL+-.
In the warning message, click OK.

All selected rows will be deleted.