How to delete N/A rows?

Question

I have an Excel workbook with multiple tabs. I want a macro to run on opening that deletes any row that has an N/A error in it.

I need the below to run on opening, and also need it to delete based on an N/A in any column not just A.

Sub RowKiller()
    Dim N As Long, NN As Long
    Dim r As Range
    NN = Cells(Rows.Count, "A").End(xlUp).Row
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    For N = NN To 1 Step -1
        Set r = Cells(N, "A")
        If wf.CountA(r) = 1 And wf.CountA(r.EntireRow) = 1 Then
            r.EntireRow.Delete
        End If
    Next N
End Sub

Here are the column headers:

Here's an example of an error line that I want to delete:

Answer 1

Here is a generic function you can use to find #N/A errors in a specified range and delete the rows. I have also included a much shorter function which would delete any error rows if that is of use.

The function finds a subset of the range with errors then tests the cell values for whether it is an #N/A error. This should be quicker than testing every cell in the used ranges.

The .SpecialCells(xlConstants, xlErrors) is probably redundant which could simplify the function, but I'm not aware of all use cases that result in #N/A so included for completeness.

https://support.microsoft.com/en-us/office/automatically-run-a-macro-when-opening-a-workbook-1e55959b-e077-4c88-a696-c3017600db44

Sub Foo()
    Call ErrorRowKiller(ActiveSheet.UsedRange)
End Sub

Function ErrorRowKiller(ErrorArea As Range)
    On Error Resume Next
    Dim R1 As Range, R2 As Range, Rall As Range, Cell As Range, ToKill As Range
    With ErrorArea
        Set R1 = .SpecialCells(xlConstants, xlErrors)
        Set R2 = .SpecialCells(xlFormulas, xlErrors)
    End With
    
    If R1 Is Nothing Then
        Set Rall = R2
    Else
        Set Rall = IIf(R2 Is Nothing, R1, Application.Union(R1, R2))
    End If
    
    For Each Cell In Rall
        If Application.WorksheetFunction.IsNA(Cell) = True Then
            If ToKill Is Nothing Then
                Set ToKill = Cell
            Else
                Set ToKill = Application.Union(ToKill, Cell)
            End If
        End If
    Next Cell
    
    ToKill.EntireRow.Delete
    
End Function

Function ErrorRowKiller2(ErrorArea As Range)
    On Error Resume Next
    With ErrorArea
        .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
        .SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
    End With
End Function