Delete All Rows With Errors in a column

Question

I am working on a VBA code and part of the code is needed to delete all the rows in the sheet where the text in the column "J" is "#N/A". I have written a code but but I get a Type mismatch error when debugging it.

Here is the code

Dim i As Long
For i = Cells(Rows.Count, 10).End(xlUp).Row To 1 Step -1
If Cells(i, 10) = "#N/A" Then Cells(i, 1).EntireRow.Delete
Next i

Answer 1

Try this code:

Dim i As Long
For i = Cells(Rows.Count, 10).End(xlUp).Row To 1 Step -1
If Cells(i, 10).Text = "#N/A" Then Cells(i, 1).EntireRow.Delete
Next i

Answer 2

Use the Range.SpecialCells method with the xlSpecialCellsValue constant as xlErrors to quickly identify all cells in column J with errors. It wasn't disclosed whether the cells were formulas or typed constants so I've added code to examine both through the xlCellType Enumeration types.

Sub del_error_rows()
    With Worksheets("Sheet3")
        On Error Resume Next
        With .Columns(10).SpecialCells(xlCellTypeFormulas, xlErrors)
            .EntireRow.Delete
        End With
        With .Columns(10).SpecialCells(xlCellTypeConstants, xlErrors)
            .EntireRow.Delete
        End With
        On Error GoTo 0
    End With
End Sub

The On Error Resume Next is necessary in case there are no cells with that particular error configuration. In that case, the SpecialCells would be Nothing and you must bypass any error thrown from attempting to handle nothing.