VBA macro to Collapse/Expand all grouped rows/columns

imaguy77

New Member
Joined
Aug 26, 2008
Messages
21
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!!
 
im trying to use the code to expand and collapse my entire "sheet1" but for some reason it's not working. Any ideas?
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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