How to expand, read and shrink the data groups in excel? RRS feed


All replies

  • Hi Prabhakaran,

    I am not aware of any built in collections that return groups but you could build your own collections based on the OutlineLevel property of Range Object

    Consider a sheet with cells A1:A10 somehow grouped

    If you run the following

    Sub Main()
        Dim sh As Worksheet
        Set sh = Sheet1
        Dim cell As Range
        For Each cell In Range("A1:A10")
            Debug.Print Rows(cell.Row).OutlineLevel
    End Sub
    You can get the .OutlineLevel returned. 

    You can then create a Collection object that stores the row number and its outline level and then manipulate further from there

    Wednesday, September 10, 2014 7:17 AM
  • Michal I am not looking for ways to group and ungroup but to expand, read and contract the grouped data. You know... like this:

    for each group in activesheet.usedrange.groups


                         for cell in group

                                  do something

                         next cell


    next group

    Anyways to do like that?

    Wednesday, September 10, 2014 7:20 AM
  • Thanks Michal. Isn't there a code to do the "Show Detail" and "Hide Detail" programmatically. Possible?
    Wednesday, September 10, 2014 8:48 AM
  • rows(rownum).showdetail = true solves my purpose. Any other effective method than this?
    Wednesday, September 10, 2014 9:19 AM