Results 1 to 14 of 14

20010626, 16:04 #1
 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 be equals zero. However, if column A contains a descriptive label (and be 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?<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20010626, 16:39 #2
 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.

20010627, 23:26 #3diegovGuest
Re: Delete Rows Equalling Zero (Excel 97 SR2)
For many years I did this sort of thing by reSorting 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

20010628, 00:52 #4
 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

20010628, 13:24 #5
 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>

20010628, 13:32 #6
 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>

20010628, 13:56 #7
 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>

20010628, 15:37 #8
 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.

20010628, 15:38 #9
 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>

20010628, 22:04 #10diegovGuest
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

20010628, 23:01 #11
 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.

20010628, 23:47 #12
 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

20010628, 23:49 #13
 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

20010628, 23:50 #14
 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