Geek Answers Handbook

Programmatically group and ungroup rows or columns

Is there a way to programmatically group / ungroup columns or rows in Excel 2010?

Note:

  • the "manual" command is in Data> Outline> Group / Ungroup
  • in excel 2003, this was used to work: someSheet.columns(i).ShowDetail = True / False , but it no longer works in 2010 for groups (only for pivot tables and intermediate groups)
  • Recording a macro does not generate any code that I could use

More precisely, calling myRange.ShowDetail = True in Excel 2010 expands the collapsed group, but throws an error if the group is already expanded. And the ShowDetail property returns True, regardless of whether the group was expanded or not.

+4
source share
3 answers

In Excel 2010, the ShowDetail property always returns true for the group, regardless of whether it has been minimized or expanded. Instead, you can use the Hidden property:

 'to expand If myRange.EntireColumn.Hidden Then myRange.EntireColumn.ShowDetail = True End If 'to collapse If Not myRange.EntireColumn.Hidden Then myRange.EntireColumn.ShowDetail = False End If
+3
source

Regarding rows not in pivot tables ... It wasn’t my experience in Excel 2010 that ShowDetail ALWAYS evaluates to True. I thought it was, but I didn’t understand that I needed to be in the summary line for this property to work as expected. Secondly, I did not understand that the resulting row is by default under grouped rows. Testing for minimized / expanded mode became much clearer as soon as I changed this parameter to have a summary line over grouped lines (in the ribbon: data> outline, display box frame).

If my selected cell is in the summary row, ShowDetail evalutes returns True if grouped records are displayed, and False if they are not. The key for me was in the summary line to see that the behavior works this way. Having default child / grouped lines really threw me away.

Here's my macro that dynamically expands and collapses grouped records tied to a summary row when I select a cell in the summary row. And that makes my cell in the Bold column if the section expands. This macro does not run if I have selected more than one cell.

Note that protecting a worksheet prevents the expansion and collapse of cell groups. My worksheet is protected, so I take sheets to expand / collapse and then replay them. (Perhaps I can just protect or protect only the current sheet, not all).

 Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'TOGGLE SHOW/HIDE ROW If Target.Cells.Count = 1 Then If (Target.EntireRow.OutlineLevel = 1) And (Target.Offset(1, 0).EntireRow.OutlineLevel = 2) And _ (Target.Column < 15) Then Call Macros.ProtShts(False) Target.EntireRow.ShowDetail = Not Target.EntireRow.ShowDetail If Target.EntireRow.ShowDetail = True Then Range(Cells(Target.Row, 1), Cells(Target.Row, 14)).Font.Bold = True Else Range(Cells(Target.Row, 1), Cells(Target.Row, 14)).Font.Bold = False End If Call Macros.ProtShts(True) End If End If End Sub

Remember, I set my summary line above grouped entries. If the summary line is lower than the grouped entries (by default), then the reference to the offset line should be changed to -1, for example:

 (Target.Offset(1, 0).EntireRow.OutlineLevel = 2)
+3
source

It’s best to just change the width of the cell column in the hidden range. This will automatically ungroup the selection.

 dim wsA as worksheet set wsA = Worksheets("Name of your Worksheet") wsA.Columns("A:AJ").Columns.Group wsA.Range("A:A").ColumnWidth = 22.22 ' make the change to one of the cells in the group that you want to unhide.
0
source

More articles:


All Articles