Forum Discussion
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!
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.
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.
