# How to delete rows that have a 0 in every column but not rows that have 1 column thats not 0

Copper Contributor

# How to delete rows that have a 0 in every column but not rows that have 1 column thats not 0

I have an excel sheet with 8000+ rows of data, there is 18 columns.

each column represents a sample, each row is a different bacteria. so in each cell is the quantity of that bacteria  (row) in that sample (column).

i want to delete the rows where each column is a zero (essentially no samples have that bacteria). Is there an easy way to do this? Because doing it by hand for 8000+ rows is taking a long time

thank you!

2 Replies

# Re: How to delete rows that have a 0 in every column but not rows that have 1 column thats not 0

In S1, enter some dummy title such as Delete.

In S2, enter the formula =COUNTIF(A2:R2, "<>0")=0

Fill down.

In rows of only zeros, the formula will return TRUE, in the others FALSE.

Turn on AutoFilter (Sort & Filter > Filter on the Home tab of the ribbon).

Click the drop-down arrow in S1, and clear the check box for FALSE:

Click OK. Only the rows with TRUE will be visible:

Select all rows from row 2 down, then right-click in the selection, and select Delete Row from the context menu.

Finally, turn off the filter.

# Re: How to delete rows that have a 0 in every column but not rows that have 1 column thats not 0

As variant you may add helper column with SUM(row) in each cell. Filter entire range on this column, select 0 only, select from top to down of filtered rows -> Delete entire sheet row.