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.