[VBA] How to Bulk Clear Formula Error Cells (#N/A, #DIV/0!)

Introduction

When processing data in Excel, you often encounter formula errors such as #N/A, #DIV/0!, and #REF!. While these errors may be necessary during the calculation process, you usually want to hide or remove them when presenting the final report to improve readability.

Manually finding and deleting error cells one by one is hard work. However, by using the SpecialCells method in VBA, you can accurately locate only the error cells in a sheet and clear them all at once.

In this article, I will introduce a simple and effective macro to achieve this.

VBA Code: Clear Formula Error Cells

The core of this macro is using the SpecialCells method to identify “cells where the result of a formula is an error” and then executing the .ClearContents method on them.

Complete Code

' Clear all formula error cells in the current sheet
Sub ClearAllFormulaErrorCells()

    ' Define variables
    Dim targetSheet As Worksheet
    Dim errorRange As Range
    
    ' Set the target sheet (currently active sheet)
    Set targetSheet = ActiveSheet
    
    ' Prevent the macro from stopping if no error cells exist
    On Error Resume Next
    
    ' Get all cells where formula results are errors
    Set errorRange = targetSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    
    ' Reset error handling
    On Error GoTo 0
    
    ' Clear content only if error cells were found
    If Not errorRange Is Nothing Then
        errorRange.ClearContents
        MsgBox "Cleared formula error cells.", vbInformation
    Else
        MsgBox "No formula error cells found.", vbInformation
    End If

End Sub

How to Use

1. Open the Visual Basic Editor (Alt + F11) and paste the code above into a standard module.
2. Activate the worksheet where you want to clear errors.
3. Run the ClearAllFormulaErrorCells macro.
4. All cells displaying errors like #N/A on the sheet will become blank.

Code Explanation

On Error Resume Next

If there are absolutely no error cells on the sheet, the SpecialCells method will return an error, causing the macro to stop. This line tells VBA to ignore that specific error and proceed to the next line.

Set errorRange = targetSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

This is the most important part of the process.

• .SpecialCells: A method to get a range of cells that match specific criteria.
• xlCellTypeFormulas: The first argument specifies targeting “cells containing formulas.”
• xlErrors: The second argument adds the condition “results that are error values.”

As a result, the errorRange variable will contain all formula error cells on the sheet.

If Not errorRange Is Nothing Then ...

If On Error Resume Next was triggered because no errors were found, the errorRange variable remains empty (Nothing). This If statement ensures that the clear operation only runs when “one or more error cells are found.”

errorRange.ClearContents

This executes the .ClearContents method on the found error cells to remove their contents. While .Clear would remove formatting as well, .ClearContents is more suitable here because we only want to remove the error values.

Summary

In this article, I introduced how to use VBA to clean up formula errors on a sheet in bulk.

• Use .SpecialCells(xlCellTypeFormulas, xlErrors) to pinpoint only error cells.
• Combine this with On Error Resume Next to ensure the macro finishes normally even if no errors exist.

Having this macro ready will dramatically speed up the process of formatting your reports. Please give it a try.