Monday, October 10, 2011

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.
Olá! Se você ainda não assinou, assine nosso RSS feed e receba nossas atualizações por email, ou siga nos no Twitter.
Nome: Email:

0 kommentarer:

Post a Comment