Expand / Collapse Rows based a Cell Value

[User2000]

Hi,

I have a cell A1 that will either be "Yes" or "No".

I have cells(rows) A2-A11 that are grouped and defaulted as collapsed/hidden. I want these rows to automatically expand if A1 is "Yes". Is this possible?

 

[mrshl9898]

Hi, you need to insert this in the sheet in question (not a module)

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim KeyCells As Range

    Set KeyCells = Range("A1")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then

        If Range("A1") = "Yes" Then
            Rows("2:11").Select
            Selection.EntireRow.Hidden = False
            Range("A1").Select
       End If
   
    End If
    
End Sub

 

[User2000]

Thanks! That did the job, however I got an error with this line and Excel crashes every time when I try to debug:

Selection.EntireRow.Hidden = False

I am not competent enough with VBA to understand why. Any further comments would be greatly appreciated!

 

[mrshl9898]

Hmm, maybe a version issue?

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)



    Dim KeyCells As Range


    Set KeyCells = Range("A1")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
        Is Nothing Then


        If Range("A1") = "Yes" Then
            Rows("2:11").Select
            On Error Resume Next
            Selection.EntireRow.Hidden = False
            Range("A1").Select
       End If
   
    End If
     
End Sub

 

[User2000]

Works perfectly! Thanks so much!