Inefficient code for hiding multiple sets of rows based on a formula driven cell value

[PaulinhoC]

Hi all, I have a worksheet 'C' where 'cell 'C5' is derived from a formula that is pulling from another worksheet. I have the following code to certain rows based on the value of 'C5'.
e.g. If 'C5'="Risk", then: rows 193:251 = Hidden ; rows 8:192 = Unhidden
If 'C5'="TEK", then rows 8:168 = Hidden; rows 169:192 = Unhidden, rows 193:251 = Hidden
If 'C5'=0 then rows 8:251 = Unhidden

I am finding that as I start filling out the worksheet with more and more data (includes functions in columns F, G, H, J, K, L), the code is running inefficient and in an endless loop when "TEK" is the value in C5.

Can someone point me to why my code is running so inefficient and endless looping when "TEK" is the value triggering which rows to hide?
Many thanks in advance.

Private Sub Worksheet_Calculate()
Dim Area As Range
Set Area = Range("C5")

Select Case Area
        Case Is = "Risk": Rows("8:192").Hidden = True
                            Rows("193:251").Hidden = False
        Case Is = "TEK":     Rows("169:192").Hidden = False
                          Rows("8:168").Hidden = True
                          Rows("193:251").Hidden = True
        Case Is = 0:        Rows("8:251").Hidden = False
End Select
End Sub

 

[johnnyL]

See if this helps:

Private Sub Worksheet_Calculate()
'
'
'   Turn Settings off
      Application.ScreenUpdating = False                                    ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                      ' Turn AutoCalculation off
        Application.EnableEvents = False                                    ' Turn EnableEvents off
'
    Dim Area As Range
'
    Set Area = Range("C5")
'
    Select Case Area
        Case "Risk"
              Rows("8:192").Hidden = True
            Rows("193:251").Hidden = False
        Case "TEK"
            Rows("169:192").Hidden = False
              Rows("8:168").Hidden = True
            Rows("193:251").Hidden = True
        Case 0
            Rows("8:251").Hidden = False
    End Select
'
'   Turn Settings back on
    Application.EnableEvents = True                                                             ' Turn EnableEvents back on
    Application.Calculation = xlCalculationAutomatic                                            ' Turn AutoCalculation back on
    Application.ScreenUpdating = True                                                           ' Turn Screen Updating back on
End Sub

 

[PaulinhoC]

See if this helps:

Private Sub Worksheet_Calculate()
'
'
'   Turn Settings off
      Application.ScreenUpdating = False                                    ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                      ' Turn AutoCalculation off
        Application.EnableEvents = False                                    ' Turn EnableEvents off
'
    Dim Area As Range
'
    Set Area = Range("C5")
'
    Select Case Area
        Case "Risk"
              Rows("8:192").Hidden = True
            Rows("193:251").Hidden = False
        Case "TEK"
            Rows("169:192").Hidden = False
              Rows("8:168").Hidden = True
            Rows("193:251").Hidden = True
        Case 0
            Rows("8:251").Hidden = False
    End Select
'
'   Turn Settings back on
    Application.EnableEvents = True                                                             ' Turn EnableEvents back on
    Application.Calculation = xlCalculationAutomatic                                            ' Turn AutoCalculation back on
    Application.ScreenUpdating = True                                                           ' Turn Screen Updating back on
End Sub

That has done the trick!!! JohnnyL you are AMAZING!!!!

Thank you so much!!

 

[johnnyL]

That has done the trick!!! JohnnyL you are AMAZING!!!!

I doubt that, but I am glad it worked for you @PaulinhoC.