Delete Rows Equalling Zero (Excel 97 SR2)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

Colin,
Your formula works great!

Thanks!!

Reply | Quote

Very Good!

Reply | Quote

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

Reply | Quote

The only version that works for me is:

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

Then use Diego’s excellent method.

Reply | Quote

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

Thanks.

Reply | Quote

Put this formula in row 1 of an empty colmun:

=If(And(A1"",SUM(B1:E1)),"Delete Me","")

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote