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.