Hide rows where multiple cells = 0

**nessyness** · 08-10-2009, 02:35 PM

Hi,

I am looking for a macro that will hide the rows when the cells in columns D:Z each = 0. I cannot use something that hides the row if the sum of the cells in that row = 0, because some of the cells may contain positive & negative values which cancel each other out within that row.

I would include code, but I can't find anything on the web or on this site which I could use. Plus, I am really bad at making these up from scratch.

Thank you,
Vanessa

**VisualRoman** · 08-10-2009, 04:39 PM

I'm not sure if I understood it right, but maybe you could try something like this:

Please Login or Register  to view this content.

**Paul** · 08-10-2009, 04:44 PM

Hello Nessyness,

The following code will hide any row in which columns D:Z all contain zeros. it uses the COUNTIF function to check for 23 zeros in those columns (23 columns).

Please Login or Register  to view this content.

**nessyness** · 08-10-2009, 06:29 PM

Thanks to both of you guys. Roman, that one didn't seem to work for the particular thing I was trying to do. Paul, if I wanted to apply this to 5 different sheets in my workbook, so I wouldn't have to repeat it, how would I do that? Also, is there a count function that would stop when there was no further data in the row (if the column numbers had to change)?

*This is the spreadsheet from hell

**JBeaucaire** · 08-10-2009, 07:32 PM

Nessyness, based on Paul's original code, this adds:
1) Applies to all sheets in workbook
2) Only evaluates the columns D through "last column with any data in row1"
3) Spots the last row on each sheet properly
4) Unhides previously hidden rows to reevaluate if they should be hidden again or not

Please Login or Register  to view this content.

Just a note, you could probably do without the row-by-row loop on each sheet if you just entered a COUNT() or COUNTIF() formula directly on the sheets and just filtered by that column, deleting the remaining visible rows all at once. If speed isn't an issue, then the macro above will be fine.

**nessyness** · 08-13-2009, 11:47 AM

Thanks JB!

**JBeaucaire** · 08-13-2009, 12:01 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)