Solved

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

Posted on 2014-02-25
6
1,508 Views
Last Modified: 2014-02-25
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.
0
Comment
Question by:wilpitz
6 Comments
 
LVL 22

Expert Comment

by:spattewar
ID: 39885764
is #N/A in value format or result of a function?
0
 
LVL 22

Accepted Solution

by:
spattewar earned 500 total points
ID: 39885781
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
 
LVL 22

Expert Comment

by:spattewar
ID: 39885784
change the range to suit your needs,
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39885793
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
 
LVL 39

Expert Comment

by:nutsch
ID: 39886299
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
 

Author Closing Comment

by:wilpitz
ID: 39886606
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question