VBA code to remove #N/A errors from a worksheet IF there are any

Question

I'm using the following line to remove any #N/A errors from a worksheet:

Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Clear

How can I reconstruct it in a way it only tries to remove #N/A errors if there are any (because there aren't in every case and I get an error).

Answer 1

The best way by a country mile is to define an extra area on the worksheet (or even another worksheet), and use the formula

=IFNA(A1, "")

or the equivalent

=IF(ISNA(A1), "", A1)

(copying obviously).

That way you don't ruin the structure of your worksheet, and a cell by cell conversion could cause problems in the calculation cycle given that #N/A tends to propagate in built-in Excel functions.

But, if you must use VBA for whatever reason, then you can detect the specific #N/A error using code of the form

If v = CVErr(xlErrNA)

Where v is a Variant containing the cell value, that's already been established as having an error value.

Answer 2

Trap the error like this:

Dim rngToFind As Range

On Error Resume Next
Set rngToFind = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If Not rngToFind Is Nothing Then
    rngToFind.Clear
End If

If you want to add a specific error type, such as #N/A, you could do this:

Dim rngToFind As Range, PossError As Range

On Error Resume Next
Set rngToFind = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If Not rngToFind Is Nothing Then
    For Each PossError In rngToFind
        If PossError.Text = "#N/A" Then PossError.Clear
    Next
End If