Hide Rows with 0's

  • Thread starter Thread starter Gazz_85
  • Start date Start date
G

Gazz_85

I have a data table and in different columns a record may have a 0 in it. I
want to be able to write a simple line of code which hides any records with a
0 in it any were in its record.

any help??????
 
Say we have values in cols A thru Y. We are interested in zeros. In Z2 enter:

=IF(COUNTIF(A2:Y2,0)=25,25,IF(COUNTIF(A2:Y2,0)=0,0,1)) and copy down

Set the AutoFilter for column Z.

To see rows with all zeros, filter for 25
To see rows no no zeros, filter for 0
TO see rows with some zeros, set the filter for 1
 
In a column to the side you could have a formula like this:

=IF(COUNTIF(A2:H2,0)>0,"hide","ok")

(assuming your data occupies columns A to H) and copy this down. Then
apply autofilter to this column and select ok from the filter drop-
down.

If you want a macro to do it for you, then record a macro while you
apply the filter, then in future you can just re-run the macro.

Hope this helps.

Pete
 
Back
Top