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