Expand rows based on cell value in VBA - multiple in worksheet

[VBA_Newbie_]

Hello,

I've been trolling this forum on how to expand grouped rows based on cell value in VBA but haven't been able to get any of the posted codes to work for me.

I have three sections of collapsed grouped rows that I would like to have automatically expand if the cell value changes to "Yes". If the cell value is "No" I would the like rows to remain collapsed. Ideally, I would also like the rows to re-collapse if the cell value changes from "Yes" to "No" or if left blank. (Not as big of a concern as getting the cells to expand initially.)

First Cell: C52
First Range: Rows 54-58

Second Cell: C59
Second Range: 61-64

Third Cell: D65
Third Range: 67-77

Any help is greatly appreciated!

 

[jmacleary]

Hi there. Put this code in the worksheet code area -

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$52" Then
        If Target.Value = "Yes" Then
            Rows("54:58").EntireRow.Hidden = False
        ElseIf Target.Value = "No" Or Target.Value = "" Then
            Rows("54:58").EntireRow.Hidden = True
        End If
    ElseIf Target.Address = "$C$59" Then
        If Target.Value = "Yes" Then
            Rows("61:64").EntireRow.Hidden = False
        ElseIf Target.Value = "No"  Or Target.Value = "" Then
            Rows("61:64").EntireRow.Hidden = True
        End If
    ElseIf Target.Address = "$D$65" Then
        If Target.Value = "Yes" Then
            Rows("67:77").EntireRow.Hidden = False
        ElseIf Target.Value = "No" Or Target.Value = "" Then
            Rows("67:77").EntireRow.Hidden = True
        End If
    End If
End Sub

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The script automatically runs when you enter Yes or No in the Ranges

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  10/21/2019  11:21:16 AM  EDT
On Error GoTo M
'First Group
If Target.Address = Range("C52").Address Then
    Select Case Target.Value
        Case "Yes"
            Rows("54:58").Ungroup
        Case "No"
            Rows("54:58").Group
    End Select
End If
'Second group
If Target.Address = Range("C59").Address Then
    Select Case Target.Value
        Case "Yes"
            Rows("61:64").Ungroup
        Case "No"
            Rows("61:64").Group
    End Select
End If

'Third group
If Target.Address = Range("D65").Address Then
    Select Case Target.Value
        Case "Yes"
            Rows("67:77").Ungroup
        Case "No"
            Rows("67:77").Group
    End Select
End If
Exit Sub
M:
MsgBox "That group may not be grouped"
End Sub

 

[My Aswer Is This]

You said:

I've been trolling this forum on how to expand grouped rows

So I assume this means Group or Ungrop

Not Hidden or Unhidden.
​

 

[VBA_Newbie_]

This worked perfectly! Thanks so much!

 

[My Aswer Is This]

Which script worked. There were two solutions provided?

This worked perfectly! Thanks so much!