Grouped rows, hide/reveal base on cell value

Hi,

I'm looking to expand/hide rows that have been grouped (Data tab, Outline section, Group). What I really want is to be able to group rows, and name them because I have multiple groups I need control over. Then have them expand or hide based on a cell value. For example I want to group row 10 to 20, and if b6 = "Blue" have the rows expand if not have them hidden. Hopefully someone can help me with this I'm not having any luck with google.

Thanks
Dave

Upload sample file please (with innocuous/sanitized data).

Okay, it's not much to see, but here you go.

Are you manually grouping or using Subtotal grouping?

I ask, since it's easier to hide rows if there isn't Subtotal or other calculation done for each grouping.

Edit: Oh and is there any logic for your grouping? I'll need better representation of your logic and format.

I was manually grouping the rows no need for calculations.

Ah as for logic I have multiple products, so I was going to create a data table with the product name and a value either a 1 or 0. Then use a vlookup to determine the value, and from that value determine if the group remains compressed or is expanded.

Here's what I did with your sample. It's only starting point. You may want to modify as needed.

Set up:
1: Data validation source range: S2:S5 (S5 for blank)
2: Added empty row after each grouping (needed to break each groups)
3: Formula in T2:T4 to find empty rows in column (each break).
In T2. Confirmed as array (CTRL + SHIFT + ENTER). Copied down.

Please Login or Register  to view this content.

4: In D3 (beside validation cell) following formula

Please Login or Register  to view this content.

5: In standard module following code. Rows(#).ShowDetail is used to control individual group.

Please Login or Register  to view this content.

6: In Worksheet module (for sheet1 in this case) Me = Activesheet, RowLevels:=2 will expand all, :=1 will collapse all.

Please Login or Register  to view this content.

See attached sample.