Auto-hide rows based on multiple cell value

[AYSHANA]

great day!

can anyone help me with my code, im trying to hide based on multiple cells, but unfortunately it will hang, i dont know what mistakes im doing.

thanks alot.


Private Sub WORKSHEET_Calculate()

Dim Result, Test As Range

Set Result = Range("E11")
Set Test = Range("H11")


Select Case Result


Case Is = "Acceptable": Rows("14:83").EntireRow.Hidden = True

Case Is = "Acceptable with Bias": Rows("14:83").EntireRow.Hidden = True

Case Is = "Unacceptable": Rows("56:80").EntireRow.Hidden = True
Rows("14:53").EntireRow.Hidden = False


Case Is = "Acceptable ungarded": Rows("14:53").EntireRow.Hidden = True

Rows("56:80").EntireRow.Hidden = False

End Select

Select Case Test

Case Is = "-": Rows("14:83").EntireRow.Hidden = True



Case Is = "Qualitative": Rows("14:53").EntireRow.Hidden = True

Rows("67:79").EntireRow.Hidden = True
Rows("56:64").EntireRow.Hidden = False


Case Is = "Quantitative": Rows("14:64").EntireRow.Hidden = True

Rows("67:79").EntireRow.Hidden = False



End Select


End Sub

 

[My Aswer Is This]

I would think you should use a sheet change event script and not a calculate script:
Like this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/19/2022  12:49:01 AM  EST
Dim Result, Test As Range
Set Result = Range("E11")
Set Test = Range("H11")

Select Case Result
    Case Is = "Acceptable": Rows("14:83").EntireRow.Hidden = True
    Case Is = "Acceptable with Bias": Rows("14:83").EntireRow.Hidden = True
    Case Is = "Unacceptable": Rows("56:80").EntireRow.Hidden = True
    Rows("14:53").EntireRow.Hidden = False

        Case Is = "Acceptable ungarded": Rows("14:53").EntireRow.Hidden = True
            Rows("56:80").EntireRow.Hidden = False
End Select

Select Case Test
    Case Is = "-": Rows("14:83").EntireRow.Hidden = True
    Case Is = "Qualitative": Rows("14:53").EntireRow.Hidden = True
        Rows("67:79").EntireRow.Hidden = True
        Rows("56:64").EntireRow.Hidden = False

    Case Is = "Quantitative": Rows("14:64").EntireRow.Hidden = True
        Rows("67:79").EntireRow.Hidden = False

End Select

End Sub

 

[My Aswer Is This]

If my suggestion does not work, please explain what is not working the way you want

 

[AYSHANA]

If my suggestion does not work, please explain what is not working the way you want

If my suggestion does not work, please explain what is not working the way you want

it works, thank you.

 

[My Aswer Is This]

it works, thank you.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.