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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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
All Courses

From novice to tech pro — start learning today.