Automate Grouping in Excel?

[danomaniac]

Is there any way to expand or collapse a data group in Excel using VBA?

 

[Nikita6003]

Yes there is - we have a report where we specify the group level for each line we report, then apply using this code (not mine, but a more clever colleague's ;-)):

'First set outline settings for Group method
With l_wksReport.Outline
  .SummaryRow = xlSummaryAbove
  .AutomaticStyles = True
End With
'Now switch the group on: l_sRefersTo = the rows to group, e.g. [b]"8:20"[/b]
l_wksReport.Rows(l_sRefersTo).Group
'Hide the details under the rows you've grouped;
'l_iGroupStart = the first row of the group - in the example row [b]8[/b]
l_wksReport.Rows(l_iGroupStart).ShowDetail = False

HTH

Cheers
Nikki

 

[Zmey2]

We use something else:

http://www.zmey.1977.ru/Excel_And_Autofilter.htm

 

[danomaniac]

Thanks Nikki.

All I needed was Rows(x).ShowDetail = False

 

[Bobo72]

Hi,

This function appears to be just what I need to use for expanding and collapsing grouped rows in my Excel sheet via VBA. However, inexperienced as I am in using VBA in Excel, could anyone provide me with an example of how to actually use this function?

Best regards and thanks in advance,
B / Denmark