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?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,134
Messages
5,484,929
Members
407,474
Latest member
Pam Sander

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top