Hiding Multiple groups of rows using VBA

[Costtx]

I have a spreadsheet where I am trying to make it hide multiple rows based on the values of specific cells. This works for the first set of values:

VBA code used:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
If Target.Column = 8 And Target.Row = 16 Then
If Target.Value = "No" Then
Application.Rows("17:19").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "Yes" Then
Application.Rows("17:19").Select
Application.Selection.EntireRow.Hidden = False
End If
End If
End Sub

I have tried duplicating it so I could perform this function with multiple selections in the same sheet, however I do not know how to include it multiple times (I do not have a great working knowledge of VBAs).

I have tried to adjust it to the following:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
If Target.Column = 8 And Target.Row = 34 Then
If Target.Value = "No" Then
Application.Rows("35:37").Select
Application.Selection.EntireRow.Hidden = True
ElseIf Target.Value = "Yes" Then
Application.Rows("35:37").Select
Application.Selection.EntireRow.Hidden = False
End If
End If
End Sub

How would I mix it in?

Thanks.

 

[sykes]

If I've understood your question correctly...

This should work; shortened the code up a little, too.
Always test on a COPY of your work, first.

If you wish to add more, just insert more "Case" statements.

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
    Select Case .Address
        Case "$H$16"
            If .Value = "No" Then
                Me.Rows("17:19").EntireRow.Hidden = True
            Else
                Me.Rows("17:19").EntireRow.Hidden = False
            End If
        Case "$H$34"
            If .Value = "No" Then
                Me.Rows("35:37").EntireRow.Hidden = True
            Else
                Me.Rows("35:37").EntireRow.Hidden = False
            End If
    End Select
End With
End Sub

 

[maabadi]

Try This:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
If Target.Column <> 8 Then Exit Sub
    Select Case Target.Row
        Case 16
            If Target.Value = "No" Then
                Application.Rows("17:19").EntireRow.Hidden = True
            ElseIf Target.Value = "Yes" Then
                Application.Rows("17:19").EntireRow.Hidden = False
            End If
        Case 34
            If Target.Value = "No" Then
                Application.Rows("35:37").EntireRow.Hidden = True
            ElseIf Target.Value = "Yes" Then
                Application.Rows("35:37").EntireRow.Hidden = False
            End If
    End Select
End Sub

 

[Costtx]

If I've understood your question correctly...

This should work; shortened the code up a little, too.
Always test on a COPY of your work, first.

If you wish to add more, just insert more "Case" statements.

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
    Select Case .Address
        Case "$H$16"
            If .Value = "No" Then
                Me.Rows("17:19").EntireRow.Hidden = True
            Else
                Me.Rows("17:19").EntireRow.Hidden = False
            End If
        Case "$H$34"
            If .Value = "No" Then
                Me.Rows("35:37").EntireRow.Hidden = True
            Else
                Me.Rows("35:37").EntireRow.Hidden = False
            End If
    End Select
End With
End Sub

I'm afraid this did not work

 

[Costtx]

Try This:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20180822
If Target.Column <> 8 Then Exit Sub
    Select Case Target.Row
        Case 16
            If Target.Value = "No" Then
                Application.Rows("17:19").EntireRow.Hidden = True
            ElseIf Target.Value = "Yes" Then
                Application.Rows("17:19").EntireRow.Hidden = False
            End If
        Case 34
            If Target.Value = "No" Then
                Application.Rows("35:37").EntireRow.Hidden = True
            ElseIf Target.Value = "Yes" Then
                Application.Rows("35:37").EntireRow.Hidden = False
            End If
    End Select
End Sub

The first one worked but the second did not.