Hide specific rows in various worksheets depending on Summary worksheet cell value

[ChristinaL]

Hi,

I'm new to VBA codes (first macro ever) and having been trying to automate Private Sub codes so that when I make one cell change (drop down selection) from the Summary worksheet it auto hides rows accordingly across various sheets.

My goal is to hide non-relevant cases/information throughout a Financial model Workbook so that it is simple for an investor to review. My struggle is that every worksheet needs different rows hidden.

These are two examples of my code on different worksheets, which is functioning as a Private Sub but I have to double click (open to edit then close) the reference cell (referring back to the Summary worksheet) to get the macro to run on that page, which is not feasible for the investors to do:

-------------------

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("j4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Realistic": Rows("11:199").EntireRow.Hidden = False
Rows("200:574").EntireRow.Hidden = True
Case Is = "Worst": Rows("388:574").EntireRow.Hidden = False
Rows("11:387").EntireRow.Hidden = True
Case Is = "Best": Rows("200:387").EntireRow.Hidden = False
Rows("11:199").EntireRow.Hidden = True
Rows("388:574").EntireRow.Hidden = True
Case Is = "ALL": Rows("11:574").EntireRow.Hidden = False
End Select
End If
End Sub
------------------

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("j4"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Realistic": Rows("66:223").EntireRow.Hidden = False
Rows("224:537").EntireRow.Hidden = True
Case Is = "Worst": Rows("381:537").EntireRow.Hidden = False
Rows("66:380").EntireRow.Hidden = True
Case Is = "Best": Rows("224:380").EntireRow.Hidden = False
Rows("66:223").EntireRow.Hidden = True
Rows("381:537").EntireRow.Hidden = True
Case Is = "ALL": Rows("66:537").EntireRow.Hidden = False
End Select
End If
End Sub
-----------------


Thanks in advance for anyone who could point me in the right direction. At the moment I am overwhelmed with so much new information in these forums.

[paulked]

Hi and welcome to the forum.

Worksheet_Change events don't trigger on a cell changing due to a formula which is why you have to double-click it.

Therefore, you could consider the Worksheet_Calculate event, something like:



Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Select Case Range("J4")
Case Is = "Realistic": Rows("11:199").EntireRow.Hidden = False
Rows("200:574").EntireRow.Hidden = True
Case Is = "Worst": Rows("388:574").EntireRow.Hidden = False
Rows("11:387").EntireRow.Hidden = True
Case Is = "Best": Rows("200:387").EntireRow.Hidden = False
Rows("11:199").EntireRow.Hidden = True
Rows("388:574").EntireRow.Hidden = True
Case Is = "ALL": Rows("11:574").EntireRow.Hidden = False
End Select
Application.EnableEvents = True
End Sub

[ChristinaL]

Thanks! That is working smoothly now! :clap:

[paulked]

You're welcome. You can mark this thread solved by using Thread Tools at the top of the page :thumb