Hides Rows based on cell value on multiple worksheets

[lonlyboy]

I have the code listed below working on a 'per worksheet' basis. I need to create multiple sheets and would like to use the same code across sheets. Instead of copying/pasting the same code into each sheet, I would like to make it workbook wide but run on the active sheet. I started playing with the first couple lines but it's not working. I'm sure that I'm just missing something simple in this code.



If ActiveSheet.Range(ActiveSheet.Cells("A1")) = "SELECT" Then
ActiveSheet.Rows("2:31").EntireRow.Hidden = True
End If

If Worksheet.Range("A1") = "Panel 1" Then
Application.Rows("2:12").EntireRow.Hidden = False
Application.Rows("13:31").EntireRow.Hidden = True
End If

If Range("A1") = "Panel 2" Then
Rows("2:12").EntireRow.Hidden = True
Rows("13:24").EntireRow.Hidden = False
Rows("25:31").EntireRow.Hidden = True
End If

If Range("A1") = "Panel 3" Then
Rows("2:24").EntireRow.Hidden = True
Rows("25:31").EntireRow.Hidden = False
End If

End Sub

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.

Note that "EntireRow" throughout your code is not needed since you are using Rows() which of course are already entire rows.

I am not certain that I have understood completely, but test this with a copy of your workbook.

Sub Hide_Show_Rows()
  Rows("2:31").Hidden = False
  Select Case UCase(Range("A1").Value)
    Case "SELECT"
      Rows("2:31").Hidden = True
    Case "PANEL 1"
      Rows("2:12").Hidden = False
    Case "PANEL 2"
      Rows("2:12").Hidden = True
      Rows("13:24").Hidden = False
      Rows("25:31").Hidden = True
    Case "PANEL 3"
      Rows("2:24").EntireRow.Hidden = True
      Rows("25:31").EntireRow.Hidden = False
  End Select
End Sub

 

[lonlyboy]

Since my original post I changed a little how i wanted to do it. Basically I'm trying to create a programming load sheet for a particular brand of access panel. On the first page I ask a question of what type of panel are you using? Depending on their answers I want it do hide/unhide rows on another sheet. That way it only shows fields needed based on the panels selected on the first page. I tried using your example above with some slight modifications but it's not working. With in the sheets line, I didn't want to have to modify the code later so wanted it to look for the sheet name in a field on that page. That field matches the worksheet name.

Sub Hide_Show_Rows()

Select Case UCase(Range("B10").Value)
    Case " - SELECT -"
     Sheets("A10").Rows("2:31").Hidden = True
    Case "LP1501"
      Sheets("A10").Rows("2:12").Hidden = False
      Sheets("A10").Rows("13:31").Hidden = True
    Case "LP1502"
       Sheets("A10").Rows("2:12").Hidden = True
       Sheets("A10").Rows("13:24").Hidden = False
       Sheets("A10").Rows("25:31").Hidden = True
    Case "LP2500"
       Sheets("A10").Rows("2:24").Hidden = True
       Sheets("A10").Rows("25:31").Hidden = False
End Select
End Sub

 

[Peter_SSs]

Since my original post I changed a little how i wanted to do it.

Try this version then.

Sub Hide_Show_Rows_v2()
  Dim ws As Worksheet
  
  Set ws = Worksheets(Range("A10").Value)
  Select Case UCase(Range("B10").Value)
    Case " - SELECT -"
      ws.Rows("2:31").Hidden = True
    Case "LP1501"
      ws.Rows("2:12").Hidden = False
      ws.Rows("13:31").Hidden = True
    Case "LP1502"
      ws.Rows("2:12").Hidden = True
      ws.Rows("13:24").Hidden = False
      ws.Rows("25:31").Hidden = True
    Case "LP2500"
      ws.Rows("2:24").Hidden = True
      ws.Rows("25:31").Hidden = False
  End Select
End Sub