Hide and Unhide Entire Rows Across Multiple Sheets Based on a Cell Value

[jbusby825]

I have a workbook that has 54 sheets (a TOC, Setup, and a tab for 52 weeks).

In the Setup sheet I have 20 products setup and I have a Y/N column next to each product to determine if that product is being used or not. What I want is, if the product has an N next to it and it is not being used, then i want to hide all the rows associated with that product in the 52 weeks. If I come in and change the N to a Y, then I want it to unhide all the rows associated with the product in the 52 weeks.

You can see in the code what I have been testing out, it's inside the Setup worksheet. It works, but I don't want to be creating this line of code for 52 separate tabs and for 20 different products. What is a better solution?

Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)

' Hiding/Unhiding Product1
   If Range("B3").Value = "N" Then
        Sheet3.Rows("10:13").EntireRow.Hidden = True
        Sheet4.Rows("10:13").EntireRow.Hidden = True
    Else
        Sheet3.Rows("10:13").EntireRow.Hidden = False
        Sheet4.Rows("10:13").EntireRow.Hidden = False
    End If


' Hiding/Unhiding Product20
   If Range("B22").Value = "N" Then
        Sheet3.Rows("85:88").EntireRow.Hidden = True
        Sheet4.Rows("85:88").EntireRow.Hidden = True
    Else
        Sheet3.Rows("85:88").EntireRow.Hidden = False
        Sheet4.Rows("85:88").EntireRow.Hidden = False
    End If
End Sub

 

[jbusby825]

No...More like if I highlight say B10:B17 and type Y and hit ctrl Enter where it will fill the Y into all the selected cells...that's when it won't recognize the change in value.

 

[igold]

The part in blue

Ws.Rows(Rw).Resize(4).Hidden = [COLOR=#0000ff]Target.Value = "N"[/COLOR]

Will return either True or False depending on the target value. So if the target=N that part returns True & so the rows are hidden

Ah, yes, I see that now.

Thanks so much. I would not have thought to go down that path (if that makes sense).

 

[igold]

Couldn't you just change the line:

If Target.CountLarge > 1 Then Exit Sub

to ">20"

 

[Fluff]

Thanks so much. I would not have thought to go down that path (if that makes sense).

You're welcome


@jbusby825
This will allow multiple changes

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ws As Worksheet
   Dim Rw As Long
   Dim Rng As Range
   If Not Target.Column = 2 Then Exit Sub
   For Each Ws In Worksheets
      For Each Rng In Intersect(Target, Range("B:B"))
         If Not Ws.Name = "TOC" And Not Ws.Name = "Setup" Then
            Rw = ((Rng.row * 3) - 2) + Rng.row
            Ws.Rows(Rw).Resize(4).Hidden = Rng.Value = "N"
         End If
      Next Rng
   Next Ws
End Sub

 

[jbusby825]

@Fluff

That was it!!! You are awesome...thank you!!!

 

[Fluff]

Glad we could help & thanks for the feedback