Expand / Collapse Row and Column Groupings using VBA

[imtryinhard]

I am trying to write a personal macro script to Expand (Show Detail) or Collapse (Hide detail) of a very large (300R x 200C worksheet) that i frequently toggle between a summary view and full model view so i don't have to keep using my mouse click on the little 1-2-3 groups in the upper left with my mouse.

I want to have it so that if the outline is expanded (=2) then collapse it, adn if it's already collapsed, expand it.

But this code isn't working

Sub CollapseExpando()

If ActiveSheet.ShowLevels.rowlevels = 2 Then
    ActiveSheet.Outline.ShowLevels rowlevels:=1
 
Else if ActiveSheet.ShowLevels.rowlevels = 1 Then
    ActiveSheet.Outline.ShowLevels rowlevels:=2
 
End If

End Sub

Help would be appreciated...thanks!

 

[imtryinhard]

I should have mentioned that i'm getting a runtime error '438' saying Object doesn't support this property or method

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this with a copy of your workbook.
The first time that you run the code after opening the workbook, level 2 should be shown. So, if it was already level 2 you will see no change. However, while the workbook is open, each running of the code should toggle the levels.

Sub CollapseExpando_v2()
  Static myLevel As Long
  
  If myLevel = 0 Then myLevel = 1
  myLevel = 3 - myLevel
  ActiveSheet.Outline.ShowLevels rowlevels:=myLevel, columnlevels:=myLevel
End Sub