Excel VBA: How to Expand and Collapse a Group

Microsoft ExcelLeave a Comment on Excel VBA: How to Expand and Collapse a Group

Excel VBA: How to Expand and Collapse a Group

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.

Related

Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top


Subscribe to our newsletter