VBA macro to Collapse/Expand all grouped rows/columns

neelpatel

New Member
Joined
Jul 24, 2012
Messages
23
im trying to use the code to expand and collapse my entire "sheet1" but for some reason it's not working. Any ideas?
 

neelpatel

New Member
Joined
Jul 24, 2012
Messages
23
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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
 

neelpatel

New Member
Joined
Jul 24, 2012
Messages
23
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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
 

JeffreyGNolan

New Member
Joined
Nov 13, 2014
Messages
1
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

New Member
Joined
Apr 4, 2015
Messages
15
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

New Member
Joined
Dec 1, 2016
Messages
1
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

New Member
Joined
Feb 9, 2017
Messages
23
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?
 

Forum statistics

Threads
1,081,476
Messages
5,358,908
Members
400,514
Latest member
JoHio2577

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top