Auto hide rows based on formula driven value

[PaulinhoC]

I am trying to create a customized version based on the individual using the workbook.

I have a 'Start' worksheet where a name is selected from drop down (C2), which will also derive the Group (C3), Role (C4) & Area (C5) of the person selected.

I have done the code so based on the name selected it will only show the worksheets that are relevant to that individual (worksheets named A, B, C, D, E or F)

What I am stuck on and would welcome any help is to hide rows within worksheet 'C' based on the Area of the individual selected from the 'Start' worksheet,
- If Area=Risk (hide: rows 8-192, show: rows 193-251)
- If Area=TEK (hide: rows 8-168, show: rows 169-192, hide: row 193-251)

Appreciate any help at all, thanks in advance!

 

[NoSparks]

Try this Change Event code.
Paste it into the Start sheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
' limit to one cell at a time
If Target.CountLarge > 1 Then Exit Sub
' monitor C5
If Target.Address = "$C$5" Then
    Application.ScreenUpdating = False
    ' value is Risk
    If Target.Value = "Risk" Then
        With Sheets("C")
            .Rows("8:251").Hidden = True
            .Rows("193:251").Hidden = False
        End With
    End If
    ' value is TEK
    If Target.Value = "TEK" Then
        With Sheets("C")
            .Rows("8:251").Hidden = True
            .Rows("169:192").Hidden = False
        End With
    End If
    Application.ScreenUpdating = True
End If
    
End Sub

 

[PaulinhoC]

Paste it into the Start sheet code module

Private Sub Worksheet_Change(ByVal Target As Range)
' limit to one cell at a time
If Target.CountLarge > 1 Then Exit Sub
' monitor C5
If Target.Address = "$C$5" Then
    Application.ScreenUpdating = False
    ' value is Risk
    If Target.Value = "Risk" Then
        With Sheets("C")
            .Rows("8:251").Hidden = True
            .Rows("193:251").Hidden = False
        End With
    End If
    ' value is TEK
    If Target.Value = "TEK" Then
        With Sheets("C")
            .Rows("8:251").Hidden = True
            .Rows("169:192").Hidden = False
        End With
    End If
    Application.ScreenUpdating = True
End If
   
End Sub

Thanks for the reply NoSpark, it works but there are 2 minor issues. Just wondering if you know how to resolve this.

1. Whenever the target.value in C5 changes to TEK, the macro has to be manually stopped as it keeps running in a loop. After pressing ESC and stopping, the end result is correct with the appropriate rows hidden.

2. I also have code in the Start sheet as the name selected in C2 determines what worksheets will be visible, I think there is an issue as they are both Worksheet_Change, is there a way that I can have both pieces of code to be run so that C2 will determine which worksheets are visible and then C5 will determine which rows are visible within specific worksheets?

Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Select Case Worksheets("Start").Range("C2").Value
        Case ""
            Worksheets("A").Visible = False
            Worksheets("B").Visible = False
            Worksheets("C").Visible = False
            Worksheets("D").Visible = False
            Worksheets("E").Visible = False
            Worksheets("F").Visible = False
        Case "Nick Fahey"
            Worksheets("A").Visible = True
            Worksheets("B").Visible = True
            Worksheets("C").Visible = True
            Worksheets("D").Visible = True
            Worksheets("E").Visible = True
            Worksheets("F").Visible = True
        Case "Nicholas Job"
            Worksheets("A").Visible = True
            Worksheets("B").Visible = True
            Worksheets("C").Visible = True
            Worksheets("D").Visible = True
            Worksheets("E").Visible = True
            Worksheets("F").Visible = True
        Case "Michael Fox"
            Worksheets("A").Visible = False
            Worksheets("B").Visible = False
            Worksheets("C").Visible = False
            Worksheets("D").Visible = False
            Worksheets("E").Visible = True
            Worksheets("F").Visible = False
        Case "Anthony Ventura"
            Worksheets("A").Visible = False
            Worksheets("B").Visible = False
            Worksheets("C").Visible = True
            Worksheets("D").Visible = False
            Worksheets("E").Visible = False
            Worksheets("F").Visible = False
    End Select
End Sub

 

[NoSparks]

1. Whenever the target.value in C5 changes to TEK, the macro has to be manually stopped as it keeps running in a loop.

??? That may be but it won't be because of C5 unless its value is being changed and changed again.
If you want to see what is triggering things put a message box as the very first line in the event

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    MsgBox Target.Address
' limit to one cell at a time
If Target.CountLarge > 1 Then Exit Sub
    'rest of procedure
    '

I don't know how you are populating C3, C4 and C5.
In the following sub you can see how I've done it for testing purposes using the Change Event monitoring C2 .

Private Sub Worksheet_Change(ByVal Target As Range)
' limit to one cell at a time
If Target.CountLarge > 1 Then Exit Sub
    
    Dim ws As Worksheet, fndRng As Range, ShtArr As Variant

' monitor C2
If Target.Address = "$C$2" Then
    Set fndRng = Sheets("Sheet3").Range("A:A").Find(What:=Target.Value, LookIn:=xlValues, Lookat:=xlWhole)
    If Not fndRng Is Nothing Then
        Range("C3") = fndRng.Offset(, 1).Value
        Range("C4") = fndRng.Offset(, 2).Value
        Range("C5") = fndRng.Offset(, 3).Value
    End If
End If

' monitor C5
If Target.Address = "$C$5" Then
    
    ShtArr = Array("A", "B", "C", "D", "E", "F")

    Select Case Worksheets("Start").Range("C2").Value
        Case ""
            Worksheets("A").Visible = False
            Worksheets("B").Visible = False
            Worksheets("C").Visible = False
            Worksheets("D").Visible = False
            Worksheets("E").Visible = False
            Worksheets("F").Visible = False
        Case "Nick Fahey"
            Worksheets("A").Visible = True
            Worksheets("B").Visible = True
            Worksheets("C").Visible = True
            Worksheets("D").Visible = True
            Worksheets("E").Visible = True
            Worksheets("F").Visible = True
        Case "Nicholas Job"
            Worksheets("A").Visible = True
            Worksheets("B").Visible = True
            Worksheets("C").Visible = True
            Worksheets("D").Visible = True
            Worksheets("E").Visible = True
            Worksheets("F").Visible = True
        Case "Michael Fox"
            Worksheets("A").Visible = False
            Worksheets("B").Visible = False
            Worksheets("C").Visible = False
            Worksheets("D").Visible = False
            Worksheets("E").Visible = True
            Worksheets("F").Visible = False
        Case "Anthony Ventura"
            Worksheets("A").Visible = False
            Worksheets("B").Visible = False
            Worksheets("C").Visible = True
            Worksheets("D").Visible = False
            Worksheets("E").Visible = False
            Worksheets("F").Visible = False
    End Select
    
    ' Risk
    If Target.Value = "Risk" Then
        For Each ws In Sheets(ShtArr)
            If ws.Visible Then
                ws.Rows("8:251").Hidden = True
                ws.Rows("193:251").Hidden = False
            End If
        Next ws
    End If
    
    ' TEK
    If Target.Value = "TEK" Then
        For Each ws In Sheets(ShtArr)
            If ws.Visible Then
                ws.Rows("8:251").Hidden = True
                ws.Rows("169:192").Hidden = False
            End If
        Next ws
    End If

End If
    
End Sub

Hope this helps you out.