VBA macro to Collapse/Expand all grouped rows/columns

[imaguy77]

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

 

[neelpatel]

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

 

[neelpatel]

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

 

[Jerry Sullivan]

Did you try this code from Post #2:

Sub Expand_All()
    ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
End Sub

If Sheet1 isn't active you could use this...

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

 

[neelpatel]

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

 

[Jerry Sullivan]

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.

Sub AutoFit_Columns()
    Cells.EntireColumn.AutoFit
End Sub

 

[neelpatel]

thanks! youre the best

 

[JeffreyGNolan]

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?

 

[Arikrishnan]

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.

 

[Grandia]

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!

 

[Tom_H]

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?