excel group with hidden rows

Question

I have an Excel sheet where several rows are grouped together using "Group and outline".

By default the group is to be expanded but I have one row that I have hidden using vba in Workbook_Open.

After I click the [-] to collapse the group and then the [+] to expand it again my hidden row is now visible.

Is there a way to keep the hidden row hidden even after the group is collapsed and expanded again? Or is there a way to capture an event so that I can use vba to hide the row every time the group is expanded?

Answer 1

From a link on another site Excel VBA Trap the Group and Outline expand/collapse (Show Detail / Hide Detail) events

What it states is

the only event you can trigger with a group collapse/expand action is the calculation event. To do so you need to use the SUBTOTAL function which is aware of whether or not rows are hidden.

I hope this helps you