How do I automatically hid rows on a cell selection?

[garygrung]

Hi

In one cell I have a Data Validation list so the users can choose a question. (Source: Choose, Automation?, No Automation?)

If they choose "Choose" I would like to hide rows 59 to 79.
If they choose "Automation?" I would like to hide rows 59 to 63.
And if they choose "No automation?" I would like to hide rows 64 to 79.


I have tried:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
Application.ScreenUpdating = False
If Range("C56:C56") = "Automation?" Then
        For i = 59 To 63
            Rows(i).RowHeight = 0
        Next i
      Else
        For i = 59 To 63
            Rows(i).RowHeight = 18.75
        Next i
    End If
End Sub

Great if I want to use one selection but not multiple.

Can you please help?

Thanks
Gary

 

[MrKowz]

Welcome to the boards!

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Rows(59 & ":" & 79).Hidden = False
Select Case Range("C56").value
    Case "Choose"
        Rows(59 & ":" & 79).Hidden = True
    Case "Automation?"
        Rows(59 & ":" & 63).Hidden = True
    Case "No automation?"
        Rows(64 & ":" & 79).Hidden = True
End Select
End Sub

 

[garygrung]

Hi Keith

What a great, neat (in both senses) solution .

Thanks for that
Gary