Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Rows Equalling Zero (Excel 97 SR2)

    In the attached spreadsheet I need to delete all rows whose sum in columns b-e equals zero. However, if column A contains a descriptive label (and b-e are blank), the row needs to stay.

    I was going to put a SUM in col F and then have a macro look for zero, but the descriptive label in column A is confusing the issue.

    Help?
    Attached Files Attached Files
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    It looks like the only rows that you might want to delete have numbers in column A (?). If that's true, put the following formula in cell F1and copy it down...
    =AND(ISNUMBER(A1),SUM(B1:E1)=0)
    You would then sort all the data with column F as the sort key. All the TRUEs would then be grouped together so that you could easily delete them.

  3. #3
    diegov
    Guest

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    For many years I did this sort of thing by re-Sorting the list and then selecting the block that I wanted gone, then Edit - Delete.

    There is, however, a much more elegant solution that will keep the original order of the rows! Simply follow these steps:

    1) Modify the suggested formula to

    =If(And(A1<>"",SUM(B1,E1)),NA(),"")

    2) Select the column that contains the formulas
    3) Press F5 (goto)
    4) From the Dialog box, select the SPECIAL button
    5) Choose the option Box that reads "Formulas"
    6) Uncheck the check boxes below that say "Number","text" and "Logicals" but leave the "Errors" one with the checkmark
    7) Select EDIT - DELETE, and choose "Entire Row" from the dialog box.

    Voila!

    After I figured this method out, I have never gone back to the old ways of sorting the list

    Cheers,

    Diego V

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    Very Good!
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    Colin,
    Your formula works great!

    Thanks!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    Legare,
    I am confused about one portion of your formula. I understand that you are saying IF A1 <> space, AND IF..
    and this is where I get confused. The formula reads SUM(B1,E1)--- shouldn't that be B1. E1(i.e. the range, rather than just the 2 cells)? And doesn't it need to say something about the SUM such as SUM(B1.E1)=0?
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    Hi Diego,
    I tried your formula and it works great also. I like the added advantage of having the rows already selected so they can be deleted or hidden.

    As usual, everyone in this forum is so helpful.

    Thanks
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    The only version that works for me is:

    =IF(AND(ISNUMBER(A1),SUM(B1:E1)=0),NA(),"")

    Then use Diego's excellent method.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    Yes, that is the version that works for me also.

    Thanks.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  10. #10
    diegov
    Guest

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    Sherry,

    my guess is that it should have had a : instead of a , as in SUM(B2:E2) - specifying a RANGE.

    Boolean (True/False) values tend to be of the form 1/0 or -1/0. A formula that evaluates to 0 is equivalent to one that evaluates to FALSE, and one that evaluates to something different than zero is equivalent to one that evaluates to TRUE.

    Therefore, saying SUM(B2:E2) inside an IF statement is equivalent (but shorter to write!) than SUM(B2:E2)<>0

    Cheers,

    Diego

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    Legare was being subtle and economical.

    In F1 put =AND(A1<>"",(SUM(B1,E1))), then put some text in A1. F1 shows FALSE. Now put a number in B1, F1 shows TRUE. TRUE and FALSE are what control if statements.

    However I think Legare should have put B1:E1 rather than B1,E1, incase only C1 and/or D1 are the only cells with numbers in the range B1 to E1.

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    Put this formula in row 1 of an empty colmun:

    <pre>=If(And(A1<>"",SUM(B1:E1)),"Delete Me","")
    </pre>


    Copy that formula down and it should tell you which rows to delete.
    Legare Coleman

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    Yes, that was a typo. It should have been B1:E1. I have corrected the original post. Thanks for catching that!
    Legare Coleman

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Rows Equalling Zero (Excel 97 SR2)

    No, Legare's fingers got ahead of his head. It should have been B1:E1.
    Legare Coleman

Posting Permissions

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