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

VBA_Newbie_

New Member
Joined
Oct 21, 2019
Messages
2
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

Well-known Member
Joined
Oct 5, 2015
Messages
854
Office Version
2007
Platform
Windows
Hi there. Put this code in the worksheet code area -
Code:
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
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,449
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

Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
16,449
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.
 

Forum statistics

Threads
1,085,835
Messages
5,386,230
Members
401,986
Latest member
crt54

Some videos you may like

This Week's Hot Topics

Top