Solved

Want to expand and collapse Excel groupings using VBA code

Posted on 2007-12-06
11
6,334 Views
Last Modified: 2012-06-27
Does anyone know how to expand and collapse Excel groupings using VBA?
0
Comment
Question by:taduh
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20423780
Hi...Lets say i have group rows from row 1 to 13...Now here is the code which will do the trick..

Sub Hi()

    Rows("1:13").Select
    Selection.EntireRow.Hidden = True
    Rows("1:13").Select
    Selection.EntireRow.Hidden = False

End Sub

The first one will hide and second one will show as grouping is nothing else but hiding of rows or coloumns...now if u want to hide and unhide coloumns let me know...will tell u how to do tht...
0
 

Author Comment

by:taduh
ID: 20423865
That might be a good alternate solution, but I've got groupings in place using the Excel groupings facility.  I click the plus sign indicator to manually expand the grouping and the minus sign indicator to manually collapse the grouping. I'm wondering if there is a way using VBA code to expand and collapse these groupings.
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20423885
Well buddy thts not a alternate solution..thts the way how do u actually do this...coz see grouping cant be captured otherwise as its for hiding and unhiding the particular set of data selected..and by this way ur actually doing grouping in vb only..
0
 
LVL 13

Expert Comment

by:rettiseert
ID: 20423917
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20423935
Man no doubt Kevin is a Genius... or Guru or Master..but the only problem that i can see in that..it will group or ungroup all the groups which has been made in excel...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:taduh
ID: 20427136
rettisert,

Hi. I get an error running the following code:

   With Range("Group1")
      Me.Rows(.Row + .Rows.Count).ShowDetail = False
   End With

It says that the Me keyword can only be used in class modules. How do I make a module a class module?

Thanks.



0
 
LVL 38

Expert Comment

by:jeverist
ID: 20427380
Hi taduh,

The 'Me' keyword is generally used in a Sheet module or a UserForm.  I think we should get rid of it and specify the worksheet and range directly.  BTW, the ShowDetail only works on one row or column at a time show we need to process each row seperately like this:

Dim ws As Worksheet, rngrow As Range

Set ws = ActiveSheet

For Each rngrow In ws.Rows("7:14") ' Change for the rows of interest
    rngrow.ShowDetail = False ' False to hide, True to show
Next rngrow

Jim
0
 

Author Comment

by:taduh
ID: 20427535
Jim,

Hi. This will work, but let me ask about something that would make it better for me:

Is there any way to setup ws.rows("7:14") as a rangename and then do the same basic thing?  I'm trying to avoid maintaining specific row numbers in the code. If I could name rows 7 thru 14 as a range and then use the ShowDetail = false command to close the group, that would be perfect!

I tried but got an error. Let me know what you think.

Thanks!
0
 
LVL 38

Accepted Solution

by:
jeverist earned 500 total points
ID: 20427712
taduh,

Sure!  Try your named range(s) with these two routines:

Sub TestHideOrShowDetail()
Dim ws As Worksheet, rng As Range

Set ws = ActiveSheet
Set rng = ws.[Detail1] ' "Detail1" is the named range on the sheet.  Change for the named range of interest

HideOrShowDetail DetailRange:=rng, ShowDetail:=True

End Sub

Sub HideOrShowDetail(ByRef DetailRange As Range, Optional ByVal ShowDetail As Boolean = False)
Dim rngrow As Range

For Each rngrow In DetailRange.Rows
    rngrow.ShowDetail = ShowDetail ' False to hide, True to show
Next rngrow

End Sub

Jim
0
 

Author Comment

by:taduh
ID: 20427957
Jim,

Most excellent! Thank you very much! Works like a charm.

taduh
0
 
LVL 38

Expert Comment

by:jeverist
ID: 20428340
taduh,

You're welcome and thanks for the grade!

Jim
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now