VBA How can I Hide Specific Groups of Rows

[TornadoThomo]

I would like to be able to hide certain groups of rows if, for example the name of the group was selected in a drop down list elsewhere in the Spread sheet.
For example, If I have a group of rows (rows 10 through to 15) and I have named the group "Derivs", How can I arrange for the group to be hidden if the name "Derivs" was selected in cell "c2" for example.

I had a solution where could achieve this by just selecting the row range (10:15), but the problem I have is with maintenance. If I need to add to the spreadsheet/cellrange at some point in the future (add a row in the middle of this range) thissolution would not take the change into account and would not include the lastrow in the new range required. Whereby I believe if I group the rows, this willaccommodate any changes I make to the group.


Thank you in advance for your help

 

[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

If you enter Derivs into Range("C2")
The named Range Derivs will be hidden

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/28/2019  12:45:56 PM  EST
If Target.Address = Range("C2").Address Then
If Target.Value = "Derivs" Then
With Range("Derivs")
    .Rows.Hidden = True
End With
End If
End If
End Sub

<colgroup><col width="96" style="width:72pt"></colgroup><tbody>
</tbody>

 

[My Aswer Is This]

Now if you wanted more values to enter into Range(C2)
If you entered John Range Named John would be hidden

Just keep adding more as you want.
You should see the logic

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  1/28/2019  12:58:56 PM  EST
On Error GoTo M
If Target.Address = Range("C2").Address Then
Select Case Target.Value
    Case "Derivs"
        With Range("Derivs")
            .Rows.Hidden = True
        End With
    Case "John"
        With Range("John")
            .Rows.Hidden = True
        End With
End Select
End If
Exit Sub
M:
MsgBox "The Range  " & Target.Value & "  Does not exist"
End Sub