Code to conditionally expand or collapse only certain grouped rows

[Rick Howard]

I have several groupings of rows in worksheet. I need to have each separate grouping be able to automatically expand or collapse based on a value in each group.

ie-
Grouping Rows 11-15
Grouping Rows 21-25
Grouping Rows 31-36

If cell values in A10, A20, or A30 = 1, then the grouping below it would need to be collapsed, and if not, then expanded.

 

[cgcamal]

Hi Rick,

Go to VBA Editor, double click on the module of the sheet you have the data. If it is "Sheet1" hit double
click on Sheet within left panel in VBA editor.

Now, from drop down list pane, select "Worksheet" and paste the code or simply do double click on
Sheet1 and paste the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("$A1:A100")) Is Nothing Then

    If Range("A10").Value = 1 Then
        Rows("11:15").EntireRow.Hidden = True
    Else
        Rows("11:15").EntireRow.Hidden = False
    End If

    If Range("A20") = 1 Then
        Rows("21:25").EntireRow.Hidden = True
    Else
        Rows("21:25").EntireRow.Hidden = False
    End If

    If Range("A30") = 1 Then
        Rows("31:36").EntireRow.Hidden = True
    Else
        Rows("31:16").EntireRow.Hidden = False
    End If
End If

End Sub

Now you can test it. If A10, A20 or A30 have values before you put this code, change to 1 and see
the required rows will turn hidden.

and, instead of 1 put another value in those cells and rows below will unhidde.

Hope this helps.

Regards

 

[Rick Howard]

Thank you cgcamal.

I was looking for a way to work with a row grouping that had already been established in the "Data", "Group & Outline" selection...

but your approach may address what I need to do better, especially if I incorporate named ranges.

Thanks... RICK

 

[excelenergy]

Hello,

This thread has helped me also - with the code aspect...but what can I trigger the expand/collapse with?

Ultimately Id like a little +/- button inside the cell I want to expand/collapse...

Is there a control that does this in the controls box? There are so many controls - not even sure where to look.

Hi Rick,

Go to VBA Editor, double click on the module of the sheet you have the data. If it is "Sheet1" hit double
click on Sheet within left panel in VBA editor.

Now, from drop down list pane, select "Worksheet" and paste the code or simply do double click on
Sheet1 and paste the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
If Not Intersect(Target, Range("$A1:A100")) Is Nothing Then
 
    If Range("A10").Value = 1 Then
        Rows("11:15").EntireRow.Hidden = True
    Else
        Rows("11:15").EntireRow.Hidden = False
    End If
 
    If Range("A20") = 1 Then
        Rows("21:25").EntireRow.Hidden = True
    Else
        Rows("21:25").EntireRow.Hidden = False
    End If
 
    If Range("A30") = 1 Then
        Rows("31:36").EntireRow.Hidden = True
    Else
        Rows("31:16").EntireRow.Hidden = False
    End If
End If
 
End Sub

Now you can test it. If A10, A20 or A30 have values before you put this code, change to 1 and see
the required rows will turn hidden.

and, instead of 1 put another value in those cells and rows below will unhidde.

Hope this helps.

Regards

 

[mail2khalsa]

Hi,

I have also got similar query but situation is slightly different...

I have got more than 1800 rows and applied 35 ungrouping's (+) to have selected ungroup to ungroup hidden rows while I need all rows to be grouped with a single macro..
I'm able to set up grouping but ungrouping seems trouble as I have 35 (+) but need one of them to ungroup at a time.. if you need, if I can enter a value above the row so it only ungoup the specific grouping

and sheet remain protected all the time...

any advise on this would be appreciated....

Regards
mail2khalsa

Hi Rick,

Go to VBA Editor, double click on the module of the sheet you have the data. If it is "Sheet1" hit double
click on Sheet within left panel in VBA editor.

Now, from drop down list pane, select "Worksheet" and paste the code or simply do double click on
Sheet1 and paste the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("$A1:A100")) Is Nothing Then

    If Range("A10").Value = 1 Then
        Rows("11:15").EntireRow.Hidden = True
    Else
        Rows("11:15").EntireRow.Hidden = False
    End If

    If Range("A20") = 1 Then
        Rows("21:25").EntireRow.Hidden = True
    Else
        Rows("21:25").EntireRow.Hidden = False
    End If

    If Range("A30") = 1 Then
        Rows("31:36").EntireRow.Hidden = True
    Else
        Rows("31:16").EntireRow.Hidden = False
    End If
End If

End Sub

Now you can test it. If A10, A20 or A30 have values before you put this code, change to 1 and see
the required rows will turn hidden.

and, instead of 1 put another value in those cells and rows below will unhidde.

Hope this helps.

Regards