Want to expand and collapse Excel groupings using VBA code

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

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.

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

Hi

Is ths what you are looking for?

https://www.experts-exchange.com/questions/21208073/Collapse-Expand-Group-from-within-code.html?sfQueryTermInfo=1+excel+expand+group+vba

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

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.

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

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!

Jim,

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

taduh

taduh,

You're welcome and thanks for the grade!

Jim