Dear Readers,

This is a small VBA tip to automate to expand or collapse groups created in Excel. Before I jump to VBA code about Expanding or collapsing the groups in excel, I would like to brief you about Grouping or Outlining Functionality in Excel.
It is always good to know “How to do it manually. before automating any step.

What is Grouping in Excel?

Most of you who use excel in their day-to-day work, know about grouping. I will explain you about grouping feature of Excel for those who does not know about this. As the name itself suggests, this is the inbuilt feature in Excel which allows users to group their Rows or Columns accordingly. Grouping can be done at Rows and Columns level both. This is called as Outlining as well.

Steps to do Grouping/Outlining in Excel?

Step 1.

Select your Rows or Columns where you want to apply the Grouping (Outlining).

Step 2.

Go to “Data” Tab in Excel Ribbon and Click on Group Button as shown in the below image:

When certain Rows or Columns are grouped then user will be able to see a + (Plus) sign to expand all the rows or columns grouped together. Once all the rows or columns are expanded then user will be able to see a – (Minus) sign to collapse them as shown in the below picture.
 

 

Is it possible to have nested Grouping?

Now what is nested grouping? As Nested means Nested grouping is Grouping within a Group. I will explain this by giving an example. Suppose you have grouped Rows 5 to 20. Within this group, create another sub-group of rows 8 to 12. Same case is with Column grouping as well. Each nesting is called as Level. Sub-grouping can be done up to 8 levels in Microsoft Excel. Refer the below picture. There are maximum of 8 Levels present. You will not be allowed to group any further level.
 

 

VBA Method used to Display Outline Levels

Outline.ShowLevels is a method which is used to show outlines of different levels. This method takes RowLevel and ColumnLevel as input.

.Outline.ShowLevels(RowLevels, ColumnLevels)

Where:

RowLevels:

This is the Row Level number UP TO which you want to show. It means if you pass this number as 7 then this method will show all the levels till 7. It will not expand the 8th Level but all the levels before 7.

ColumnLevels

This is the Column Level number UP TO which you want to show. It means if you pass this number as 7 then this method will show all the Column levels till 7. It will not expand the 8th Level but all the levels before 7.

VBA Code to Expand all Outline Levels

As mentioned earlier there can be a maximum of 8th Level in Row and Column grouping. Therefore to expand all the levels available in a sheet you can pass the maximum possible level number i.e. 8 for both Row and Column Level numbers.

Sub Expand_All()
    ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
End Sub

VBA Code to Collapse all Outline Levels

There is only one method .ShowLevels is available. There is no method for hiding all the levels. But if you understand clearly the meaning of parameters passed as RowLevels and ColumnLevels then you will be able to achieve this as well using the same method.
 
As mentioned earlier LevelNumber are the level number to display all the levels UP TO that level ONLY. It means all the levels beyond that will not be shown. Hence if I pass Row and Column Parameter as 1 then only first Level will be displayed and all other levels will be hidden and that is how we achieved both the goals (Expand All & Collapse All) with the same method.

Sub Collapse_All()
    ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
End Sub

If you want any specific level to be shown in Row or Column levels, you can achieve it by changing the parameters value.