Delete Data

Change the sheet name according to your requirement.

Try this. It creates a Range to delete, so will be slightly faster than deleting individual rows

Option Explicit

Sub DeleteErrors()
    Dim rRng As Range, rCl As Range, rDelete As Range
    With ActiveSheet
        On Error GoTo exit_proc
        Set rRng = .Range(.Cells(1, 8), .Cells(.Rows.Count, 8).End(xlUp).SpecialCells(xlCellTypeFormulas))
        On Error GoTo 0
        On Error Resume Next
        For Each rCl In rRng
            If Application.WorksheetFunction.IsNA(rCl) Then

                If rDelete Is Nothing Then
                    Set rDelete = rCl
                Else: Set rDelete = Union(rDelete, rCl)
                End If
            End If
        Next rCl
        If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
    End With
    Exit Sub
exit_proc:
    MsgBox "No formulas found"
End Sub

Select allOpen in new window

Hi,
The 1st code also deletes all the rows at once, not one by one.

Choose the best one :)

I'm assuming #N/A is the part of formula result which you want to delete then you can do this without running a loop or checking each row in faster manner by simply doing this..

Sub deleterng()

    Dim rng As Range, lrow As Long

    lrow = Cells(Cells.Rows.Count, "h").End(xlUp).Row

    Set rng = Range("H1:H" & lrow).SpecialCells(xlCellTypeFormulas, 16)

    If Not rng Is Nothing Then rng.EntireRow.Delete

End Sub

Select allOpen in new window

However if its a value and not a formula..then use this code..This will give the same result only difference is whether the #N/A values which you are checking is value or answer of a formula...

Sub deleterng()

    Dim rng As Range, lrow As Long

    lrow = Cells(Cells.Rows.Count, "h").End(xlUp).Row

    Set rng = Range("H1:H" & lrow).SpecialCells(xlCellTypeConstants, 16)

    If Not rng Is Nothing Then rng.EntireRow.Delete

End Sub

Select allOpen in new window

Saurabh...

If its a repeated effort then using VBA is probably best way but if it is infrequent, just use Auto Filter. Apply filter and select #NA in the drop down for column H. Select all visible rows as a block will only delete those that are visible, leaving the rest intact when the filter is disabled.

Thanks
Rob H