Hide rows based on max value of column

[mgchurch77]

I am trying to run some code that will hide a row if a certain max value of a column is reached. I am fine with any solution to this whether it be that the macro determines the max from a column and then runs the hide function or if the max value is determined on the sheet with a Max(B2:B50) function. The latter is the approach that I have recently tried however I don't think that the "Private Sub Worksheet_Change(ByVal Target As Range" approach is picking up the change in the target cell which contains the MAX(B2:B50) funciton as when I force the sheet to hit the max value that is my target value, nothing hides.

 

[RomulusMilea]

Hello,

Please post the code you have tried. Thanks.

 

[mgchurch77]

Where the target cell contains the MAX() function:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$U$97" Then
Application.EnableEvents = False
If Target.Value = 2 Then
Rows("98:99").EntireRow.Hidden = True
Else
Rows("98:99").EntireRow.Hidden = False
End If
Application.EnableEvents = True
End If
End Sub

 

[RomulusMilea]

Hi there,

Please insert below code in the module of the sheet where you want to have those two rows hidden/unhidden:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
 
    If ThisWorkbook.ActiveSheet.Cells(97, 21).Value = 2 Then
 
    ThisWorkbook.ActiveSheet.Range("U98:U99").EntireRow.Hidden = True
    Else
    ThisWorkbook.ActiveSheet.Range("U98:U99").EntireRow.Hidden = False
    End If
 
    Application.EnableEvents = True
End Sub

I have tried it myself on a sample file, it works just fine.

Please confirm the code I propose above solves your request, as expected. Thank you.

 

[mgchurch77]

Worked like a dream. Thanks. I will mark this one as resolved, have a good day.