If this is your first visit, be sure to check out the Board FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.

Click here to reset your password. You MUST have a valid e-mail address so that you may receive the instructions to complete the reset.

VBA macro to Collapse/Expand all grouped rows/columns

Hi all,

I'd like to be able to run a macro which would collapse or expand all grouped rows and columns for all selected worksheets. When I say collapse or expand, i don't need any selection dialog box or anything...just the code which would accomplish it. Thanks!!

Re: VBA macro to Collapse/Expand all grouped rows/columns

I'd like to be able to run a macro which would collapse or expand all grouped rows and columns for all selected worksheets.

You can merge the code I posted with the method of iterating through
selected worksheets that Domenic provided you in your recent post.

The result for Expand_All would look like this.

Code:

Sub Expand_All()
Dim sh As Object
For Each sh In ActiveWindow.SelectedSheets
If TypeName(sh) = "Worksheet" Then
sh.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
End If
Next sh
Set sh = Nothing
End Sub

Re: VBA macro to Collapse/Expand all grouped rows/columns

This works great for expanding all rows/columns. I'm having some trouble searching online to find code to expand only one group of columns. I'm running Excel 2010. Googling around, I'm finding code such as:

Range("B:C").Columns.ShowDetail = True

Range("B:C").Columns.ShowLevels ColumnLevels:=2

I'm also guessing with code such as:

Range("B:C").Outline.ShowLevels ColumnLevels:=2

Columns("B:C").Columns.ShowLevels ColumnLevels:=2

I keep getting a "Runtime error 438: Object doesn't support this property or method".

Forgive me but I'm almost a total novice when it comes to writing code in VBA. My experience to date is recording clunky macros and then messing with the resulting code. Clicking on a particular "+" for a group doesn't generate any code when recording.