Clear Cells Based On Value Excel VBA Macro

There are several ways to clear cells based on cell value with Excel VBA. The easiest, and probably also most efficient way to do it is using the Replace method of the Range object, which can replace the target value with an empty string in a given range or entire worksheet. Another possibility is looping through all cells in a range and setting a condition to clear the contents for a target value. This gets slower the bigger the range. On the other hand, this method allows not only to clear values, but also to change formatting or delete the target cell, for example. It also allows to set various conditions, instead of a single value.

Macro code:

 
  Sub ClearCells_Method1()
 
      Dim rng As Range
 
      Set rng = ActiveSheet.UsedRange
 
      rng.Replace "NULL", ""  'short version
 
      rng.Replace What:="NULL", _
              Replacement:="", _
              LookAt:=xlPart, _
              SearchOrder:=xlByRows, _
              MatchCase:=False
 
  End Sub
 
  Sub ClearCells_Method2()
 
      Dim rng As Range
 
      Set rng = ActiveSheet.UsedRange
 
      On Error Resume Next
      For Each cell In rng
          If cell.Value = "NULL" Then cell.ClearContents  'Example1
          If cell.Value = 0 Then cell.ClearContents  'Example2
          If cell.Value >= 0 And cell.Value < 0.01 Then cell.ClearContents  'Example3
      Next
 
  End Sub
  

 

Macro explained:

• We declare and set a Range object variable to whatever target range where we want to clear cell values. The example above targets the used range in the active sheet, but could specifically be a given range or column (Range("A1:B4"), Columns("C"), etc.). We could target the entire worksheet with ActiveSheet.Cells.

• Method 1 uses the Replace method of the Range object to replace the value that meets the criteria with an empty string, which can either be given with empty double quotes or vbNullString. The short version uses Replace with only two parameters: the target value and the replacement string (empty string to clear cells). The longer version shows those two along with other parameters accepted by the Replace method that can help targeting a specific value:
• What is the string that you want Microsoft Excel to search for
• Replacement is the replacement string (What and Replacement are the same parameters used in the short version)
• LootAt specifies whether a match is made against the whole of the search text (xlWhole) or any part of the search text (xlPart)
• SearchOrder specifies the order in which to search the range and can either be  xlByRows or xlByColumns
• MatchCase makes the search case-sensitive when True 

• Method 2 loops through each cell in the target range and clears the cell value when certain criteria is met. That criteria can be a certain value, a range or values, various values, etc. That’s an advantage compared to the previous method. Furthermore, this other method allows to not just clear values but also perform other actions such as formatting the cell, copying the cell value, or deleting the cell or row altogether. Note that we use ClearContents to just clear the value while keeping the cell formatting. We could do the same setting the value of the cell to an empty string as done in method 1.

This is how we clear cells based on value in Excel using VBA macros.

Other examples:

• How to Add Conditional Formatting To Unique Values with Excel VBA macros
• How to Send Multiple Emails with Excel VBA macros
• How to Convert Range To HTML Table with Excel VBA macros