Hide Rows based on cell with drop down values

[mboufleur]

Hello everyone, I´m trying to activate a macro for my spreadsheet that automatically hides some rows based on a drop down list.

The drop down list has values such as "UHD-HDR" and "UHD-SDR", so I´m not sure they are causing the issue.

The code is the following, but nothing really happens

Private Sub HideRowsBasedOnValues(ByVal Target As Range)

    Dim ws As Worksheet
    Dim cellValue As Range

    'Set reference to the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change "Sheet1" to your sheet's name

    'Read the value from cell B22
    Set ws.cellValue = Range("B22")
    
    If Intersect(Target, cellValue) Is Nothing Then Exit Sub

    'Unhide all rows before applying hiding
    Rows("A29:A50").EntireRow.Hidden = False

    'Hide rows based on the value from cell B22
    Select Case ws.cellValue
            Case Is = ""
            Rows("A29:A50").EntireRow.Hidden = False
        Case Is = "UHD-HDR"
            Rows("A39:A50").EntireRow.Hidden = True
        Case Is = "UHD-SDR"
            Rows("A29:A38").EntireRow.Hidden = True
    End Select
End Sub

Could anyone help me understand why is this code not working? I´m using Excel 365 MSO Version 2307.

 

[kevin9999]

Please try the following on a copy of your workbook. Right click the Sheet1 tab name, select View Code, and put the code in the window that appears on the right of the screen.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B22"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Rows("29:50").Hidden = False
        Select Case Target.Value
            Case Is = "UHD-HDR"
                Rows("39:50").Hidden = True
            Case Is = "UHD-SDR"
                Rows("29:38").Hidden = True
        End Select
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub