Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

  1. #1
    New Member
    Join Date
    Aug 2008
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!!

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,486
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    The Outline.ShowLevels method allows you to show a specified number of
    RowLevels and Column Levels.

    To Collapse all Rows and Columns...
    Code:
    Sub Collapse_All()
        ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
    End Sub
    To Expand all Rows and Columns...
    Code:
    Sub Expand_All()
        ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
    End Sub
    And anything in between....
    Code:
    Sub Collapse_Rows_to_Level2_Expand_All_Columns()
        ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=8
    End Sub
    Using Excel 2016

  3. #3
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,486
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    I just noticed you want to apply this to all on selected worksheets.

    Quote Originally Posted by imaguy77 View Post
    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
    You can adapt the other variations the same way.
    Using Excel 2016

  4. #4
    New Member
    Join Date
    Aug 2008
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thank you!! This is so helpful.

  5. #5
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,486
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    Glad to hear that helped.
    Using Excel 2016

  6. #6
    New Member
    Join Date
    Jul 2012
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Any help is appreciated.

  7. #7
    New Member
    Join Date
    Jul 2012
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I also find it strange that

    Rows(9).ShowDetail = True

    works to expand a particular group of rows but

    Columns(C).ShowDetail = True

    does not work for columns.

  8. #8
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,486
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    Hi and Welcome to the Board,

    You could use either of these...

    Code:
    Columns("C").ShowDetail = True
    
    Columns(3).ShowDetail = True
    Using Excel 2016

  9. #9
    New Member
    Join Date
    Jul 2012
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Ah. Rookie mistake. Thank you!

  10. #10
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,486
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    Quote Originally Posted by jbaccash View Post
    Ah. Rookie mistake. Thank you!
    You're welcome.
    Using Excel 2016

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com