VBA Hide/Unhide rows on other sheet

[MikeIke]

Can someone please help me with the below code:

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Sheet2")
Select Case Target.Address(False, False)
Case "B12"
.Rows(14).Rows.Hidden = Target.Value = ""
.Rows(34).Rows.Hidden = Target.Value = ""
.Rows(37).Rows.Hidden = Target.Value = ""
Case "B13"
.Rows(15).Rows.Hidden = Target.Value = ""
.Rows(35).Rows.Hidden = Target.Value = ""
.Rows(38).Rows.Hidden = Target.Value = ""
End Select
End With
End Sub

I need to change it so that it hides a range of rows, say (6:26) instead of adding a line of code for each of the rows from 6 to 26 that I want to hide

Also how can I go about hiding the same rows on 5 more sheets, can I add more sheetnames after "Sheet2"?

Thanks,

Mike

 

[Jerry Sullivan]

Hi Mike and Welcome to MrExcel,

Here's one way to code that...

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim lNdx As Long
 Dim sAddressRef As String, sSheetname As String
 Dim vSheetList As Variant

 Select Case Target.Address(False, False)
   Case "B12"
    '--modify with range reference of rows
    '  to be hidden/unhidden
      sAddressRef = "6:26"
   Case "B13"
      sAddressRef = "7:32"
   Case Else
      'do nothing
      GoTo ExitProc
 End Select

 On Error GoTo ExitProc
 With Application
   .ScreenUpdating = False
   .EnableEvents = False
 End With

 '--modify with sheets to be processed
 vSheetList = Array("Sheet2", "Sheet4", "Sheet7")

 For lNdx = LBound(vSheetList) To UBound(vSheetList)
   '--on each worksheet in list...
   sSheetname = vSheetList(lNdx)
   With Sheets(sSheetname)
      '--hide rows if changed cell is nullstring, otherwise unhide
      .Range(sAddressRef).EntireRow.Hidden = Len(Target.Value) = 0
   End With
 Next
   
ExitProc:
 With Application
   If Not .ScreenUpdating Then _
      .ScreenUpdating = True
   .EnableEvents = True
 End With
 If Err.Number <> 0 Then
   MsgBox "An error occurred while trying to hide " _
      & "or unhide rows in sheet: " & sSheetname
 End If
End Sub