In Microsoft Excel it is possible to group rows and columns. To expand or collapse 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.
