Show/hide rows and sheets based on other cell values

Question

I have a workbook with a lot of worksheets.
On Sheet1, I have a lot of "sections".
In each of these "sections", I have a cell (e.g., B31), that can be either "Yes" or "No".

If the cell is "Yes", then I want to:

• show rows 32 to 46 on the same sheet (Sheet1)
• show rows 19 to 36 on a different sheet (Sheet 2)
• show Sheet3

If the cell is "No", then I want to:

• hide rows 32 to 46 on the same sheet (Sheet1)
• hide rows 19 to 36 on a different sheet (Sheet 2)
• hide Sheet3

This is the premise for 30 different "sections" on Sheet1.
Each of these section will evaluate a "Yes" or "No" value in different cells and show/hide the next 15 rows in the same sheet, show/hide rows Sheet2, and hide a certain worksheet entirely (ranging from Sheet3 to Sheet 30).

I have it working, but I feel I've coded it poorly.
Maintenance on this will be a bear if someone adds sections.
There are times I get a black screen for a few seconds because it is taking a long time to run.

The code is repeated for each section. The code below is for three sections.

Is there a way I can code this better for reusability and performance?

Dim ScopeChange As Range
Dim Module_1 As Variant
Dim Module_2 As Variant
Dim Module_3 As Variant

Module_1 = Range("B31").Value
Module_2 = Range("B49").Value
Module_3 = Range("B67").Value

Set ScopeChange = Range("B31")
If Not ScopeChange Is Nothing Then
    Application.EnableEvents = False
    Select Case Module_1
        Case "Yes": Rows("32:46").EntireRow.Hidden = False
                    Worksheets("Sheet2").Rows("19:36").EntireRow.Hidden = False
                    Worksheets("Sheet3").Visible = True
        Case "No": Rows("32:46").EntireRow.Hidden = True
                    Worksheets("Sheet2").Rows("19:36").EntireRow.Hidden = True
                    Worksheets("Sheet3").Visible = False
    End Select
    Application.EnableEvents = True
End If

Set ScopeChange = Range("B49")
If Not ScopeChange Is Nothing Then
    Application.EnableEvents = False
    Select Case Module_2
        Case "Yes": Rows("50:64").EntireRow.Hidden = False
                    Worksheets("Sheet2").Rows("37:54").EntireRow.Hidden = False
                    Worksheets("Sheet4").Visible = True
        Case "No": Rows("50:64").EntireRow.Hidden = True
                    Worksheets("Sheet2").Rows("37:54").EntireRow.Hidden = True
                    Worksheets("Sheet4").Visible = False
    End Select
    Application.EnableEvents = True
End If

Set ScopeChange = Range("B67")
If Not ScopeChange Is Nothing Then
    Application.EnableEvents = False
    Select Case Module_3
        Case "Yes": Rows("68:82").EntireRow.Hidden = False
                    Worksheets("Sheet2").Rows("55:72").EntireRow.Hidden = False
                    Worksheets("Sheet5").Visible = True
        Case "No": Rows("68:82").EntireRow.Hidden = True
                    Worksheets("Sheet2").Rows("55:72").EntireRow.Hidden = True
                    Worksheets("Sheet5").Visible = False
    End Select
    Application.EnableEvents = True
End If

End Sub

Answer 1

• Regardless of the number of cells that are changed, the code always evaluates all sections (30 or even more in the future) to hide/unhide rows and sheets. This can significantly increase processing time.

• Change: utilize change event code to validate only the changed cell and adjust the visibility of related rows and sheets.

• Another advantage is that you won't have to modify the code if users add a new section, as long as the layout of the section follows the same pattern.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .CountLarge = 1 And .Column = 2 And .Row > 30 Then
            If ((.Row - 13) Mod 18 = 0) And Len(.Value) > 0 Then
                Dim iIndex As Long, rngSht1 As Range, rngSht2 As Range
                iIndex = (.Row - 13) / 18
                Application.EnableEvents = False
                Set rngSht1 = Me.Cells(iIndex * 18 + 14, 1).Resize(15).EntireRow
                Set rngSht2 = Worksheets("Sheet2").Cells(iIndex * 18 + 1, 1).Resize(18).EntireRow
                Select Case UCase(.Value)
                Case "YES"
                    rngSht1.Hidden = False
                    rngSht2.Hidden = False
                    Worksheets("Sheet" & iIndex + 2).Visible = True
                Case "NO"
                    rngSht1.Hidden = True
                    rngSht2.Hidden = True
                    Worksheets("Sheet" & iIndex + 2).Visible = False
                End Select
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub