+ Reply to Thread
Results 1 to 4 of 4

Delete row if formula results in "FALSE" OR "#N/A" - Formula please, no macro

Hybrid View

darkfeld Delete row if formula results... 02-04-2014, 11:43 AM
RobertMika Re: Delete row if formula... 02-04-2014, 01:39 PM
darkfeld Re: Delete row if formula... 02-04-2014, 01:45 PM
RobertMika Re: Delete row if formula... 02-04-2014, 03:36 PM
  1. #1
    Registered User
    Join Date
    01-14-2013
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    14

    Delete row if formula results in "FALSE" OR "#N/A" - Formula please, no macro

    I have one spreadsheet that is pulling data using a vlookup. If the data is not available, it results as #N/A. I am attempting to have the information from the spreadsheet mentioned above pulled into a new spreadsheet that will delete the lines if they return "N/A" in the original spreadsheet. Any ideas?

    For example,
    Cells with values
    A1
    A2
    A3
    A8
    A9
    A10
    A11

    Cells returning "#N/A"
    A4
    A5
    A6
    A7
    A12
    A13

    I need to have the following information displayed in the new worksheet
    A1
    A2
    A3
    Empty row
    A8
    A9
    A10
    A11

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Delete row if formula results in "FALSE" OR "#N/A" - Formula please, no macro

    Why for A4 is empty row and the rest not?
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    01-14-2013
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Delete row if formula results in "FALSE" OR "#N/A" - Formula please, no macro

    I have 13 rows with data, then an empty line. Another 13 rows of data that follows and then another empty line. I have 10 groups that have 13 lines but the 13 lines within each group may not have information that needs to be pulled and will show up as #N/A. How do I get the information from each group to feed over to a new spreadsheet but to skip the lines that show #N/A? Does that make sense?

  4. #4
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Delete row if formula results in "FALSE" OR "#N/A" - Formula please, no macro

    Array formula:
    =IFERROR(INDEX(Sheet1!$A$1:$A$30,SMALL(IF(NOT(ISNA(Sheet1!$A$1:$A$30)),ROW(Sheet1!$A$1:$A$30)-ROW(Sheet1!$A$1)+1),ROWS($A$2:A2))),"")
    Na.xlsx
    And Custom Format
    0;-0;;@

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to delete cells containing a formula that results in ""
    By adrianodl in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-23-2012, 08:21 AM
  2. Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" not working
    By redders in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 03:52 PM
  3. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  4. Replies: 7
    Last Post: 05-13-2006, 05:02 PM
  5. [SOLVED] If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ...
    By Maria J-son in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2006, 08:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1