# Thread: Delete Rows Equalling Zero (Excel 97 SR2)

1. ## 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?

2. ## 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. ## 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

Very Good!

5. ## Re: Delete Rows Equalling Zero (Excel 97 SR2)

Colin,
Your formula works great!

Thanks!!

6. ## 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?

7. ## 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

8. ## 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. ## Re: Delete Rows Equalling Zero (Excel 97 SR2)

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

Thanks.

10. ## 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. ## 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. ## 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.

13. ## 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!

14. ## Re: Delete Rows Equalling Zero (Excel 97 SR2)

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

#### Posting Permissions

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