下面是一个通用函数，可以用来查找指定范围内的#N/A错误并删除行。我还包含了一个更短的函数，如果有用的话，它会删除任何错误行。
该函数查找包含错误的区域子集，然后测试单元格值是否为#N/A错误。这应该比测试所用区域中的每个单元格要快。
.SpecialCells(xlConstants, xlErrors)可能是冗余的，它可以简化函数，但我不知道所有导致#N/A的用例，因此为了完整性而包括在内。
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