Excel VBA: Expand and Collapse Group

In Excel it is possible to group rows and columns. To expand or collapse a a group you can use the VBA method called ShowLevels. In the example below we are expanding a row group. We have also disabled automatic recalculation to improve the performance.

' Show Level

Public Sub ShowGroupLevel(ByVal intRowLevel As Integer)

    Dim intCalculation As XlCalculation

    ' Get the old calculation state

    intCalculation = Application.Calculation

    ' Disable re-calculation

    Application.Calculation = xlCalculationManual

    ' Show the specified level (expanded)

    On Error Resume Next

    Call ActiveWorkSheet.Outline.ShowLevels(RowLevels:=intRowLevel)

    On Error GoTo 0

    ' Restore automatic calculation

    Application.Calculation = intCalculation

End Sub

Please notice that expand and collapse work in the same way. Collapsing a group is the same as only displaying the first level. Expand is the same as showing level 2 or more.