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

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.
wilpitzAsked:
Who is Participating?
 
spattewarConnect With a Mentor Commented:
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
            Else
                Set rngDelete = Union(rngDelete, rngCell)
            End If
        End If
    Next
   
    If Not rngDelete Is Nothing Then
        rngDelete.EntireRow.Delete
    End If
   
    Set rngCell = Nothing
    Set rngDelete = Nothing

End Sub

hope this helps
0
 
spattewarCommented:
is #N/A in value format or result of a function?
0
 
spattewarCommented:
change the range to suit your needs,
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Rgonzo1971Commented:
Hi,

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
                c.EntireRow.Delete
                Exit For
            End If
        End If
    Next
Next
End Sub

Open in new window

Regards
0
 
nutschCommented:
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.

Thomas
0
 
wilpitzAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.