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

Posted on 2014-02-25
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.
Question by:wilpitz
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
LVL 22

Expert Comment

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

Accepted Solution

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
                Set rngDelete = Union(rngDelete, rngCell)
            End If
        End If
    If Not rngDelete Is Nothing Then
    End If
    Set rngCell = Nothing
    Set rngDelete = Nothing

End Sub

hope this helps
LVL 22

Expert Comment

ID: 39885784
change the range to suit your needs,
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

ID: 39885793

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
                Exit For
            End If
        End If
End Sub

Open in new window

LVL 39

Expert Comment

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.


Author Closing Comment

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.

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