Macro to collapse all groupings in a workbook

KMG

New Member
Joined
Jan 15, 2009
Messages
32
Hello,

I am trying to write a macro to collapse all groupings (rows and columns) on each worksheet within a workbook. The following is what I have, but it does not seem to be doing anything. When I run this macro my screen shows "Calculating: (16 Processor(s)): x%" in the bottom right hand corner. It runs through this several times from 1-100% but does not seem to do anything as far as collapsing the rows and columns goes. Any help would be greatly appreciated.


Sub CollapseGroupings()
'Collapses all rows and columns on each worksheet of a workbook
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
ActiveSheet.Outline.ShowLevels RowLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Next wsSheet

End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi KMG,

When I run your macro (excel 2010) it collapses all grouping on the active worksheet only. With the small tweak below it works on all worksheets:

Code:
Sub CollapseGroupings()

'Collapses all rows and columns on each worksheet of a workbook
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Outline.ShowLevels RowLevels:=1
wsSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
Next wsSheet

End Sub

Let me know if this works for you!

Cheers,
alx7000
 
Upvote 0
My experience is that Excel recalcs when hiding/unhiding rows, so you may want to set calc to manual while all this is going on:

Code:
Dim myCalc as Integer

    myCalc = Application.Calculation
    Application.Calculation = xlCalculationManual

    '//your code

    Application.Calculation = myCalc
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top