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

wilpitz
wilpitz used Ask the Experts™
on
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.
Comment
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
            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
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

Commented:
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
Top Expert 2008

Commented:
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

Author

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.

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