Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

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

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

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

    im trying to use the code to expand and collapse my entire "sheet1" but for some reason it's not working. Any ideas?

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

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

    Im trying to use the code to expand and collapse my entire "sheet1" but for some reason it's not working. Any ideas?

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

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

    Did you try this code from Post #2:

    Code:
    Sub Expand_All()
        ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
    End Sub
    If Sheet1 isn't active you could use this...

    Code:
    Sub Expand_All()
        Sheets("Sheet1").Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
    End Sub
    Using Excel 2016

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

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

    My columns A and B are overlapping so its crammed. I put in the code hoping it would collapse/expand, but it won't work for some reason..

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

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

    Are your Columns A and B using Outline Grouping?
    The Collapse/Expand code will only work with Grouped Rows and Columns.

    If your Columns A and B are just too narrow for the text in those cells, try AutoFit.

    Code:
    Sub AutoFit_Columns()
        Cells.EntireColumn.AutoFit
    End Sub
    Using Excel 2016

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

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

    thanks! youre the best

  7. #17
    New Member
    Join Date
    Nov 2014
    Location
    Hamden, CT
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Similar problem but none of the above techniques including Columns(3).ShowDetail = True seem to handle it.

    I have a 3 level column outline: (months, quarters, year) in a sheet with column detail for day, and column totals for week, month, quarter and year.
    I'm writing a macro that will allow the user to select a month from a drop down menu (keyed to the column number of the month total) then automatically expand and print that month's daily detail, while collapsing all other months and quarters.

    So the question is how to expand at one, level 3 selection?

  8. #18
    New Member
    Join Date
    Apr 2015
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi All,

    This is Arikrishnan & my first visit here. Am preparing a checklist in excel. To groom my checklist I have decided to amend it with some macro programs. The structure of my file is that, it consists of 5 topics. Each topic has been made with tabs where it gets collapse/expand on double clicks.

    This was prepared with following macro:

    Sub Macro1()
    ' Macro1 Macro ' '

    Rows("7:21").Select
    If Rows("7:21").Hidden = True Then Selection.EntireRow.Hidden = False
    Else Selection.EntireRow.Hidden = True
    End If
    End Sub

    Now am looking to create a button where clicking on it should expand all 5 topics at the same time and other one to collapse all 5 topics at the same time.

    I don't now even a single line in macro. Just looking on some programs online, am learning it.
    Can someone please help me with coding as requested.

  9. #19
    New Member
    Join Date
    Dec 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi,

    Sorry. Im a newbie in excel vba. I would like to know how to expand and collapse (with + and -) of certain rows and columns in a sheet. For example, I would like to group cells A20 to L32.

    Thanks a lot!

  10. #20
    New Member
    Join Date
    Feb 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    But if I send a table as an argument to a sub/function : sub Text(tlb as Listobject)
    Then I don't know the name of the workbook or sheet...
    How can I expand gouped columns/rows of the table then?

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
  •  


DMCA.com