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

 

[igold]

Am I correct to assume that if Range("B3") corresponds to Rows("10:13") then does Range("B4") correspond to Rows(14:17).

If that is correct, then I have Product 20 or Range("B22") corresponding to Rows("86:89"). Please show a couple of more Ranges and their corresponding Rows, so a pattern is developed.

 

[jbusby825]

you are correct...I had product20 off. Good catch.

 

[igold]

Does this get you any closer to what you want. I would put this code into a code module and trigger it with a CommandButton on the "Setup" page... Additionally, I assumed that where you showed Sheet3 and Sheet4, you were using those as a test instead of putting in your full 52 weeks of sheets/tabs.

Sub test()


    Dim wsSU As Worksheet: Set wsSU = Worksheets("Setup")
    Dim ct As Integer, i As Integer, frst As Integer, scnd As Integer
    
    For ct = 1 To ThisWorkbook.Worksheets.Count
        If Not Worksheets(ct).Name = "TOC" And Not Worksheets(ct).Name = "Setup" Then
            For i = 3 To 22
                frst = ((i * 3) - 2) + i: scnd = frst + 3
                If wsSU.Range("B" & i) = "N" Then Worksheets(ct).Rows(frst & ":" & scnd).EntireRow.Hidden = True
                If wsSU.Range("B" & i) = "Y" Then Worksheets(ct).Rows(frst & ":" & scnd).EntireRow.Hidden = False
             Next
        End If
    Next
    
End Sub

I hope this helps.

 

[jbusby825]

This is perfect. Is it possible to do it without assigning it to a button? where it would just automatically happen based on the Y/N value?

Thank you!!!

 

[Fluff]

How about

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

 

[igold]

@Fluff

Nice.

I have a question... Why do the rows change from hidden to not hidden if the target changes From "N" to "Y".

igold

 

[Fluff]

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

 

[jbusby825]

@Fluff

That worked great!!! Thank you so much!

I don't understand one thing that is listed in that code but i appreciate it.

The only thing I noticed in it is that you can't mass change the Y/N column. You have to change them one at a time which is not a big deal, but wanted to point it out.

Thanks!!!

 

[Fluff]

Are you copy/pasting data into the sheet?