3

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?

ekad
  • 14,436
  • 26
  • 44
  • 46
sepang
  • 4,640
  • 4
  • 23
  • 23

1 Answers1

6

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

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • Just a quick tip for others, make sure to hook into the Calculate event and not the Change event which I was doing first otherwise it won't work. – Adam Mar 14 '16 at 22:13