CODEWITHSUNDEEP
vbaexcelexcel-2010
assylias
assylias

Reputation: 328913

Programmatically group and ungroup rows or columns

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

Note:

More precisely, calling myRange.ShowDetail = True in Excel 2010 does expand a collapsed group, but raises an error if the group is already expanded. And the ShowDetail property returns True, whether the group is expanded or not.

Upvotes: 8

Views: 40589

Answers (3)

Mark2222
Mark2222

Reputation: 1

Best is to just change the column width of a cell in the range you hide. 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.

Upvotes: 0

Dru
Dru

Reputation: 31

Regarding rows not in pivot tables ... It has NOT been my experience in Excel 2010 that ShowDetail ALWAYS evaluates to True. I thought it did but I didn't realize that I needed to be on the summary row for this property to work as expected. Second of all, I didn't realize the summary row by default is UNDER the grouped rows. Testing for collapsed/expanded became much clearer once I changed that setting to have the summary row above the grouped rows (in the Ribbon: Data > Outline, Show the Outline Dlg Box).

If my selected cell is on the summary row, the ShowDetail evalutes to True if the grouped records are showing, and to False if they are not. The key for me was being on the summary row to see that behavior work this way. Having the child/grouped rows above by default really threw me.

Here's my macro, which dynamically expands and collapses the grouped records tied to the summary row when I select a cell on a summary row. And, it makes my cell in column A bold if the section is expanded. This macro does not run if I've selected more than one cell.

Note that worksheet protection prevents expanding and collapsing groups of cells. My worksheet is protected, so I unprotect the sheets to expand/collapse then reprotect them after. (A possible improvement would be for me to just unprotect/protect just the current sheet instead of all of them.)

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 row to be above the grouped records. If your summary row is below the grouped records (the default) then the offset row reference must be changed to -1, like this:

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

Upvotes: 3

assylias
assylias

Reputation: 328913

In Excel 2010, the ShowDetail property always return true for a group, whether it is collapsed or expanded. The Hidden property can be used instead:

'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

Upvotes: 5

Related Questions