Excel VBA Delete all #N/A

VBA code to delete all #N/A error in Excel

#N/A error can occur as a return code from any Excel formula like Vlookup, Lookup etc.,

If you copy paste data only from a worksheet that has formula, then this #N/A error will occur as a string value.

You can remove this #N/A from the current sheet using this macro for the above 2 scenarios.

1. Delete all #N/A in Excel Formula

This VBA code will delete all #N/A from active sheet. It will loop thru all the cells that has error values as result of formula.

Then, if the results from the formula is error, the code will check if it is specific to #N/A. If so, delete the error.

The code is just so simple.

Sub Delete_All_hashNA()
    'Delete all #N/A error in active sheet - officetricks
    Dim iCell As Range
    
    'Loop Thru each cell with error
    For Each iCell In Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    
        'Check if it is #N/A error
        If iCell.Text = "#N/A" Then
            'Delete value but retain formula as string
            iCell = "'" & iCell.Formula
            
            'Delete error value & formula
            'iCell = ""
            
            'Change cell Background color
            iCell.Interior.Color = vbYellow
        End If
        
        'Process Next Cell
    Next

End Sub

The issue with this macro is that: It will replace the formula if there is a #N/A. It means you will not be able to analyze what went wrong and which cell had error.

So, we have given 2 options within the macro. Either you can replace both formula and error value.

Or you can delete both.

Either way the macro will highlight the cell with error with yellow background.

2. Delete all #N/A in string format

This VBA code will replace #N/A with empty string.

Sub replace_NA_Strings()
    
    'Remove #N/A error
    Cells.Replace "#N/A", ""
End Sub

This will not work on formula errors. Only cells with string will be processed.