Macro to conditionally hide/unhide rows

Hello,

I am using a quantitaive reporting table with several hundred rows that shows (by using COUNTIFS) the number of instances across a dataset in which particular multiple conditions are TRUE. I have 5 categories with varying options or classifications under each category. In total there are 1080 unique combinations of conditions acorss the 5 categories. The ultimate output on each row is a count of the number of instances in which each combination of conditions occurs across a dataset held in the same worksheet. The vast vast majority will (and should) return a count of 0. I would like to automatically hide the rows that return a result of 0 (and unhide those rows if the result changes from 0). I realise that a simple filter can achieve this but I already have a filter applied elsewhere in the sheet (and it doesn't seem to be possible to filter multiple ranges in the same worksheet). I'm therefore looking for some VBA code that might do the job instead. My (extremely) rudimentary attempts aren't getting me far.

Grateful for any assistance that anyone can offer.

This macro will hide the row of every cell in range SearchRange with a value of 0

Please Login or Register  to view this content.

Many thanks for that code Nicky C. It works very nicely to hide the rows where the result is 0. I don't necessarily need to macro to run automatically upon the worksheet being opened, but it would be useful if it responded automatically to changes to the data. At the moment, I have to manually re-run the macro after data changes to make sure they are reflected. Is there a way to incorpoate a worksheet change event into the macro?

Also, I need it to be able to automatically unhide rows when the result changes from 0 to non-zero.

Thanks for your help.

Hi
the macro should change rows without 0s from hidden to unhidden automatically when the macro is run

to run it every time the sheet is changed right click on the sheet's name in the name tab, select "view code" and copy this as a worksheet change macro like this:

Please Login or Register  to view this content.

be warned, though, if you sheet is large or complex this can slow things dowm as the macro runs whever you change the sheet.
also, you may want to think about how you change data in the sheet - every time you enter a new value it will re-hide all the rows with 0s

Very helpful once again. Good points about it slowing tings down. I might leave it to be run at user discretion rather than automatically in response to any worksheet change. This will now sound realy silly (and will horibly expose my limited knowldge) but once the macro has been run, short of selecting all the rows and using the 'unhide' option, how would I get the full table visible again (I guess I mean un-run the macro?).

Thanks again!

patel45 - the results (i.e. 0 or non-0) are formula driven based on data entered in rows that would remain visible (i.e. outside the range to which the macros would apply). Therefore (I think) it's possible for the result in a hidden row to change whilst it is hidden (in other words, I don't think the fact that it would be hidden by the macro would prevent the formulas from functioning).

Thank you for your help. I have tweaked the coding to suit my purposes so will mark this thread as solved.

Thanks again.