Excel VBA: Clear Contents If Cell Contains Specific Values

In this article we will demonstrate how to use VBA code to clear the contents of a cell if it contains various content, such as a numeric value, date, text, specific value, or blank and conditional formatting.

Example 1 – Clear Contents If Cell Contains Numeric Value

To clear cells if they contain numeric values, we will utilize the VBA IsNumeric function.

To illustrate, we’ll use the following dataset that contains several numeric values. We’ll clear the contents of the cells containing numbers using VBA code.

Using VBA code requires the Developer tab, which is not displayed on the ribbon by default. If you don’t see it, follow the link to learn how to display the Developer tab from the Customize Ribbon option.

Steps:

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

This will open up the Visual Basic window.

Go to the Insert tab at the top.
Select the Module option.

As a result, a Module code window will appear.

Enter the following code in the Module window:

Sub clearContents1()
Dim cell, rng As Range
Set rng = Range("B4:C9")
For Each cell In rng
If IsNumeric(cell) = True Then
cell.clearContents
Else
End If
Next cell
End Sub

Save the code.
Close the Visual Basic window.
Go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

The Macro dialog box will appear.

Select clearContents1 from the Macro name section.
Click on Run.

All the cells containing numeric values are cleared.

VBA Code Explanation:

Sub clearContents1()

Provides a name for the sub-procedure of the macro.

Dim cell, rng As Range

Declares the necessary variable for the macro.

Set rng = Range("B4:C9")

Sets the range of the dataset on which to apply the code.

For Each cell In rng
If IsNumeric(cell) = True Then
cell.clearContents
Else
End If
Next cell

Takes a For Each loop and sets a condition for the If statement. If the cell value is numeric, the contents will be cleared. Then the loop jumps to the next cell and repeats the process.

End Sub

Ends the sub-procedure of the macro.

Method 2 – Remove Contents If Cell Contains Text

Now we’ll use the dataset below and clear the contents of the cells containing text using the IsText function.

Steps

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

This will open up the Visual Basic window.

Go to the Insert tab at the top.
Select the Module option.

As a result, a Module code window will appear.

Enter the following code in the Module window:

Sub ClearContents2()
Dim cell, rng As Range
Set rng = Range("B4:C9")
For Each cell In rng
If Application.WorksheetFunction.IsText(cell) Then
cell.clearContents
Else
End If
Next cell
End Sub

Save the code.
Close the Visual Basic window.
Go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

The Macro dialog box will appear.

Select ClearContents2 from the Macro name section.
Click on Run.

All the cells containing text are removed.

VBA Code Explanation:

Sub ClearContents2()

Provides a name for the sub-procedure of the macro.

Dim cell, rng As Range

Declares the necessary variable for the macro.

Set rng = Range("B4:C9")

Sets a range in which to apply the code.

For Each cell In rng
If Application.WorksheetFunction.IsText(cell) Then
cell.clearContents
Else
End If
Next cell

Applies a For Each loop and sets a criterion for the If statement. If the cell value is text then it will clear the contents. Then the loop jumps to the next cell and repeats the procedure.

End Sub

Ends the sub-procedure of the macro

Method 3 – Delete Contents If Cell Contains Date

Now, using the dataset below, we will utilize the IsDate function to clear all cells containing a date.

Steps

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

This will open up the Visual Basic window.

Go to the Insert tab at the top.
Select the Module option.

As a result, a Module code window will appear.

Enter the following code in the Module window:

Sub ClearContents3()
Dim cell, rng As Range
Set rng = Range("B4:C9")
For Each cell In rng
If IsDate(cell) = True Then
cell.clearContents
Else
End If
Next cell
End Sub

Close the Visual Basic window.
Go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

The Macro dialog box will appear.

Select ClearContents3 from the Macro name section.
Click on Run.

All the cells containing dates are cleared.

VBA Code Explanation:

Sub ClearContents3()

Provides a name for the sub-procedure of the macro.

Dim cell, rng As Range

Declares the necessary variable for the macro.

Set rng = Range("B4:C9")

Sets a range in which to apply the code.

For Each cell In rng
If IsDate(cell) = True Then
cell.clearContents
Else
End If
Next cell

Takes a For Each loop and sets a condition for the If statement. If the cell value is a date then it will clear the content. Then the loop jumps to the next cell and repeats the process.

End Sub

Ends the sub-procedure of the macro.

Method 4 – Clear Contents If Cell Contains a Specific Value

Here, if a cell contains a specified value, the code will clear it.

Steps

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

This will open up the Visual Basic window.

Go to the Insert tab at the top.
Select the Module option.

As a result, a Module code window will appear.

Enter the following code in the Module window:

Sub clearContents4()
 Dim myRange As Range
 Dim iCell As Range
 Dim myValue As Long
 Set myRange = ThisWorkbook.Worksheets("Specific Value").Range("B4:C9"
 myValue = 96
 For Each iCell In myRange
 If iCell.Value = myValue Then iCell.clearContents
 Next iCell
End Sub

Save the code.
Close the Visual Basic window.
Go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

The Macro dialog box will appear.

Select clearContents4 from the Macro name section.
Click on Run.

All the cells containing a specific value (96) are cleared.

VBA Code Explanation:

Sub clearContents4()

Provides a name for the sub-procedure of the macro.

Dim myRange As Range
Dim iCell As Range
Dim myValue As Long

Declares the necessary variable for the macro.

Set myRange = ThisWorkbook.Worksheets("Specific Value").Range("B4:C9")
myValue = 96

Specifies the worksheet name and range, and then sets the specific value.

For Each iCell In myRange
If iCell.Value = myValue Then iCell.clearContents
Next iCell

Takes a For Each loop and sets a criterion for the If statement. If the icell.value is equal to the value specified above, then it will clear the content. Then the loop jumps to the next cell and repeats the procedure.

End Sub

Ends the sub-procedure of the macro

Method 5 – Erase Contents of a Row If Cell Contains Blank

Here, we’ll use a dataset that includes several client details including email and contact no. If any cell is blank, the code will clear the entire row.

Steps

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

This will open up the Visual Basic window.

Go to the Insert tab at the top.
Select the Module option.

As a result, a Module code window will appear.

Enter the following code in the Module window:

Sub ClearContents5()
For Each cell In Range("B4:E11")
If cell.Value = "" Then
cell.EntireRow.clearContents
Else
End If
Next cell
End Sub

Save the code.
Close the Visual Basic window.
Go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

The Macro dialog box will appear.

Select ClearContents5 from the Macro name section.
Click on Run.

All the cells containing blanks are cleared.

VBA Code Explanation:

Sub ClearContents5()

Provides a name for the sub-procedure of the macro.

For Each cell In Range("B4:E11")
If cell.Value = "" Then
cell.EntireRow.clearContents
Else
End If
Next cell

Takes a For Each loop for a given condition and sets a criterion for the If statement. If the cell value is blank, then it will clear the contents of the current row. Then the loop jumps to the next cell and repeats the procedure.

End Sub

Ends the sub-procedure of the macro.

Method 6 – Clear Contents If Cell Contains Conditional Formatting

In our final example, if a cell contains any conditional formatting, the code will clear the contents.

Steps:

First, we need to create conditional formatting using VBA code.

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

This will open up the Visual Basic window.

Go to the Insert tab at the top.
Select the Module option.

As a result, a Module code window will appear.

Enter the following code in the Module window:

Sub Conditional_Formatting()
Dim cell, rng As Range
Set rng = Selection
rng.Interior.Color = vbRed
End Sub

VBA Code Explanation:

Sub Conditional_Formatting()

Provides a name for the sub-procedure of the macro.

Dim cell, rng As Range

Declares the necessary variable for the macro.

Set rng = Selection

Sets the range by the selection, which means you can select cells from the dataset to use as the range.

rng.Interior.Color = vbRed

Sets the interior color as red.

End Sub

Ends the sub-procedure of the macro.

Save the code.
Close the Visual Basic window.
Select several cells to apply conditional formatting. For contiguous cells, press Shift and then select the first and last cells. For non-contiguous cells, press Ctrl and select the cells one-by-one.

Go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

The Macro dialog box will appear.

Select Conditional Formatting from the Macro name section.
Click on Run.

The selected cells will be formatted according to the code.

Now we’ll clear the contents of the cells containing conditional formatting.

Go to the Developer tab on the ribbon.
Select the Visual Basic option from the Code group.

This will open up the Visual Basic window.

Go to the Insert tab at the top.
Select the Module option.

As a result, a Module code window will appear.

Enter the following code in the module window:

Sub ClearContents6()
Dim cell, rng As Range
Set rng = Range("B4:C9")
For Each cell In rng
If cell.Interior.Color = vbRed Then
cell.Clear
Else
End If
Next cell
End Sub

Close the Visual Basic window.
Go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

The Macro dialog box will appear.

Select ClearContent6 from the Macro name section.
Click on Run.

All the cells containing conditional formatting are cleared.

VBA Code Explanation:

Sub ClearContents6()

Provides a name for the sub-procedure of the macro.

Dim cell, rng As Range

Declares the necessary variable for the macro.

Set rng = Range("B4:C9")

Sets a range for the process.

For Each cell In rng
If cell.Interior.Color = vbRed Then
cell.Clear
Else
End If
Next cell

Takes a For Each loop and sets a criterion for the If statement. If the cell’s interior color is red, then it will clear the cell. Then, the loops jumps to the next cell and does the same.

End Sub

Ends the sub-procedure of the macro.

Download Practice Workbook

Clear Contents If Cell Contains.xlsm

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Lisa Hoffer Jun 13, 2024 at 4:52 AM

Hey there, question. Is there a VBA or macro solution to clear the contents of the data in nonsequential rows? For example, I need to clear the contents of rows 4, 7, 10, and 14 on a sheet called “Sunday Report.” Thank you in advance for your assistance.
- Reply
  Lutfor Rahman Shimanto Jun 13, 2024 at 12:09 PM
  Hello Lisa Hoffer
  
  Thanks for visiting our blog and sharing such an interesting question. You wanted an Excel VBA sub-procedure to clear the contents of non-contiguous rows. I have developed such a sub-procedure to fulfil your goal. Please check the following:
  
  Excel VBA Sub-procedure:
```
Sub ClearSpecificRows()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sunday Report")
    
    Dim rowsToClear As Variant
    rowsToClear = Array(4, 7, 10, 14)
    
    Dim i As Integer
    For i = LBound(rowsToClear) To UBound(rowsToClear)
        ws.Rows(rowsToClear(i)).ClearContents
    Next i
    
    MsgBox "Contents cleared for rows 4, 7, 10, and 14 in 'Sunday Report'"

End Sub
```
  Hopefully, you have found the VBA macro you were looking for. I have attached the workbook used to solve your problem. Good luck.
  
  DOWNLOAD SOLUTION WORKBOOK
  
  Regards
  Lutfor Rahman Shimanto
  Excel & VBA Developer
  ExcelDemy

Excel VBA: Clear Contents If Cell Contains Specific Values

Example 1 – Clear Contents If Cell Contains Numeric Value

Method 2 – Remove Contents If Cell Contains Text

Method 3 – Delete Contents If Cell Contains Date

Method 4 – Clear Contents If Cell Contains a Specific Value

Method 5 – Erase Contents of a Row If Cell Contains Blank

Method 6 – Clear Contents If Cell Contains Conditional Formatting

Related Articles

Leave a reply Cancel reply