Solved

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

Posted on 2014-02-25
6
1,799 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 51

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

724 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