Delete rows if it has a value of #N/A

wilpitz used Ask the Experts™
I am trying to write a macro in Excel 2010 that looks at the values in F3 to H500 and if there is a value of #N/A in any of the cells I want to delete the row. I had some luck doing it via auto-filter but I want to put this into a macro format.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
is #N/A in value format or result of a function?
if those are values then you can use this macro. note that iserror could be any error value in the cells for e.g. #REF or #Value or #N/A etc

Sub Macro3()

    Dim rngCell As Excel.Range
    Dim rngDelete As Excel.Range
    For Each rngCell In Range("D2:F5").Cells
        If IsError(rngCell) Then
            If rngDelete Is Nothing Then
                Set rngDelete = rngCell
                Set rngDelete = Union(rngDelete, rngCell)
            End If
        End If
    If Not rngDelete Is Nothing Then
    End If
    Set rngCell = Nothing
    Set rngDelete = Nothing

End Sub

hope this helps
change the range to suit your needs,
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Top Expert 2016


pls try

Sub Macro32()
For Idx = 500 To 3 Step -1
    For Each c In Range("F" & Idx & ":H" & Idx)
        If IsError(c.Value) Then
            If c.Value = CVErr(xlErrNA) Then
                Exit For
            End If
        End If
End Sub

Open in new window

Top Expert 2008

Have you tried recording your autofilter as a macro? This is a good first step to start getting into the wonderful world of macro writing.



This worked GREAT! I always try to do everything first via the macro recorder but I could not get this one to work via that.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial