Excel VBA: Clear Contents If Cell Contains Specific Values

Sometimes you need to clear contents if the cell contains several items in Excel VBA. It can be a numeric value, text, date, specific value, or blank. You can easily clear contents if the cell contains those things using Excel VBA. This article will show how to clear contents if cell contains. I hope you find this article very interesting and gain lots of knowledge regarding the VBA code.

Excel VBA to Clear Contents If Cell Contains: 6 Suitable Examples

To clear contents if the cell contains using Excel VBA, we have found 6 examples through which you can do the job quite easily. In this article, we will remove the contents if the cell contains a numeric value, date, text, specific value, or blank and conditional formatting. In all of these examples, we utilize VBA code. We will also show the explanation of every code to have a better understanding.

1. Clear Contents If Cell Contains Numeric Value

Our first example is basically when the cell contains numeric values. In that case, we will utilize the VBA IsNumeric function in the VBA code and do some coding regarding this case. If the dataset contains any numeric value, the code will remove it from the dataset. To do this example, we take a dataset that contains several numeric values. The task is to clear the contents using the VBA code. You may not find the Developer tab by default. First, you need to display the Developer tab from the Customize Ribbon option. To use this VBA code, follow the steps carefully.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select clearContents1 from the Macro name section.
After that, click on Run.

As a result, all the cells containing numeric values are cleared.

🔎 VBA Code Explanation:

Sub clearContents1()

First of all, provide a name for the sub-procedure of the macro

Dim cell, rng As Range

Next, declare the necessary variable for the macro.

Set rng = Range("B4:C9")

After that, set a range of your dataset where you want to apply your code.

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

Then, take a for each loop and set a condition for the If statement. If the cell value is numeric then, it will clear the content. Otherwise, it will give nothing. After that, go to the next cell and repeat the process.

End Sub

Finally, end the sub-procedure of the macro.

2. Remove Contents If Cell Contains Text

Our second example is basically when the cell contains the text. In that case, we will utilize the IsText function VBA code and do some coding regarding this case. If the dataset contains any text, the code will remove it from the dataset. To do this example, we take a dataset that contains several texts. The task is to clear the contents using the VBA code.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select ClearContents2 from the Macro name section.
After that, click on Run.

As a result, you will see all the cells containing texts are removed.

🔎 VBA Code Explanation:

Sub ClearContents2()

First of all, provide a name for the sub-procedure of the macro

Dim cell, rng As Range

Next, declare the necessary variable for the macro.

Set rng = Range("B4:C9")

After that, set a range of your dataset where you want to apply your code.

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

Then, apply the for each loop and set a criterion for the If statement. If the cell value is text then, it will clear the contents. Otherwise, it will return nothing. Then, go to the next and repeat the procedure.

End Sub

Finally, end the sub-procedure of the macro

3. Delete Contents If Cell Contains Date

Our third example is basically when the cell contains the date. In that case, we will utilize the IsDate function VBA code and do some coding regarding this case. If the dataset contains any date, the code will remove it from the dataset. Follow the steps carefully.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select ClearContents3 from the Macro name section.
After that, click on Run.

As a result, you will see all the cells containing dates are eliminated.

🔎 VBA Code Explanation:

Sub ClearContents3()

First of all, provide a name for the sub-procedure of the macro

Dim cell, rng As Range

Next, declare the necessary variable for the macro.

Set rng = Range("B4:C9")

After that, set a range of your dataset where you want to apply your code.

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

Then, take a for each loop and set a condition for the If statement. If the cell value is a date then, it will clear the content. Otherwise, it will give nothing. After that, go to the next cell and repeat the process.

End Sub

Finally, end the sub-procedure of the macro.

4. Clear Contents If Cell Contains a Specific Value

Our fourth example is basically when the cell contains a specific value. In that case, we will utilize the VBA code and do some coding regarding this case. If the dataset contains any specific value, the code will remove it from the dataset. Follow the steps carefully.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select clearContents4 from the Macro name section.
After that, click on Run.

As a result, you will see all the cell containing a specific value is eliminated.

🔎 VBA Code Explanation:

Sub clearContents4()

First of all, provide a name for the sub-procedure of the macro.

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

Next, declare the necessary variable for the macro.

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

After that, specify the worksheet name and range. Then, also set the specific value.

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

Then, take a for each loop and set a criterion for the If statement. If the icell.value is equal to my specific value then, it will clear the content. Otherwise, it will return nothing. Then, go to the next cell and do the same procedure.

End Sub

Finally, end the sub-procedure of the macro

5. Erase Contents of a Range If Cell Contains Blank

Our fifth example is basically when the cell contains a blank. In that case, we will utilize the VBA code and do some coding regarding this case. If the dataset contains any blank, the code will remove the entire row from the dataset. Here, we take a dataset that includes several client details including email and contact no. In this dataset, we have a blank cell. Using this dataset, we want to solve this example.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select ClearContents5 from the Macro name section.
After that, click on Run.

As a result, you will see all the cells containing blanks are eliminated.

🔎 VBA Code Explanation:

Sub ClearContents5()

First of all, provide 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

Then, take a for each loop for a given condition and set a criterion for the If statement. If the cell value is blank, then, it will clear the contents. Otherwise, it will return nothing. After that, go to the next cell and do the same procedure.

End Sub

Finally, end the sub-procedure of the macro.

6. Clear Contents If Cell Contains Conditional Formatting

Our fifth example is basically when the cell contains conditional formatting. In that case, we will utilize the VBA code and do some coding regarding this case. If the dataset contains any conditional formatting, the code will remove the contents from the dataset. Follow the steps carefully.

Steps

First, we need to create conditional formatting using VBA code.
To do this, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

🔎 VBA Code Explanation:

Sub Conditional_Formatting()

First of all, provide a name for the sub-procedure of the macro.

Dim cell, rng As Range

Next, declare the necessary variable for the macro.

Set rng = Selection

Then, set the range by the selection which means you can select the cells from the dataset and it will take as a range.

rng.Interior.Color = vbRed

After that, set the interior color as red.

End Sub

Finally, end the sub-procedure of the macro

Then, close the Visual Basic window.
select several cells where you want to apply conditional formatting. For contiguous cells, press Shift and then, select the cells. For non-contiguous cells, press Ctrl and then, select the cells

Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select Conditional Formatting from the Macro name section.
After that, click on Run.

As a result, you will see the selected cell will be formatted according to your code.

Then, we need to clear the contents if the cell contains conditional formatting.
Go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
Then, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select ClearContent6 from the Macro name section.
After that, click on Run.

As a result, you will see all the cells containing conditional formatting are eliminated.

🔎 VBA Code Explanation:

Sub ClearContents6()

First of all, provide a name for the sub-procedure of the macro.

Dim cell, rng As Range

Next, declare the necessary variable for the macro.

Set rng = Range("B4:C9")

Then, set a range of the process.

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

After that, take a For Each loop and set a criterion for the If statement. If the cell’s interior color is red then, it will clear the cell. Otherwise, it will do nothing. Then, go to the next cell and do the same.

End Sub

Finally, end the sub-procedure of the macro.

Download Practice Workbook

Download the practice workbook below.

Clear Contents If Cell Contains.xlsm

Conclusion

We have shown 6 different examples to clear contents if the cell contains several things using Excel VBA. In this article, we utilize the VBA code to show all the examples and include an explanation of every code. I hope we covered all the possible areas regarding the topic. If you have any further questions, feel free to ask in the comment box.

Get FREE Advanced Excel Exercises with Solutions!

Excel VBA: Clear Contents If Cell Contains Specific Values

Excel VBA to Clear Contents If Cell Contains: 6 Suitable Examples

1. Clear Contents If Cell Contains Numeric Value

2. Remove Contents If Cell Contains Text

3. Delete Contents If Cell Contains Date

4. Clear Contents If Cell Contains a Specific Value

5. Erase Contents of a Range If Cell Contains Blank

6. Clear Contents If Cell Contains Conditional Formatting

Conclusion

Related Articles

Leave a reply Cancel reply