如何根据另一个工作表上的单元格值在多个工作表中隐藏/显示行

Question

我正试图想出一种更有效、更快的运行方式来调整这个公式，这样它就可以根据“输入”选项卡上的D10的值在多个工作表中隐藏/显示相同的行。

我已经尝试了我能找到的所有方法，但看起来我必须在第一个工作表的正下方列出与新工作表名称完全相同的行引用。

Private Sub Worksheet_Change(ByVal Target As Range)

Application.DisplayAlerts = False 'Prevents general pop-ups
Application.Calculation = xlManual 'Formulas are not calculated
Application.ScreenUpdating = False 'What the user see's on screen will not change

If Intersect(Target, Range("D10")) Is Nothing Then Exit Sub

'Hides all major city information so just the titles are shown.
If Target.Address = ("$D$10") And Target.Value = "" Then
Sheets("Weekly Report - New").Unprotect
    Sheets("Weekly Report - New").Rows("54:63").EntireRow.Hidden = True
    Sheets("Weekly Report - New").Rows("68:77").EntireRow.Hidden = True
    Sheets("Weekly Report - New").Rows("82:91").EntireRow.Hidden = True
    Sheets("Weekly Report - New").Rows("96:105").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("18:31").EntireRow.Hidden = False
            Sheets("Weekly Report - New").Rows("23:47").EntireRow.Hidden = True

'Unhides 'London' under the major cities section and keeps the rest hidden.
ElseIf Target.Address = ("$D$10") And Target = "UK" Then
Sheets("Weekly Report - New").Unprotect
    Sheets("Weekly Report - New").Rows("54").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("68").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("82").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("96").EntireRow.Hidden = False
            Sheets("Weekly Report - New").Rows("55:63").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("69:77").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("83:91").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("97:105").EntireRow.Hidden = True
                    Sheets("Weekly Report - New").Rows("24:28").EntireRow.Hidden = False
                    Sheets("Weekly Report - New").Rows("18:23").EntireRow.Hidden = True
                    Sheets("Weekly Report - New").Rows("29:47").EntireRow.Hidden = True

'Unhides 'French Riviera' & 'Paris' under the major cities section and keeps the rest hidden.
ElseIf Target.Address = ("$D$10") And Target = "France" Then
Sheets("Weekly Report - New").Unprotect
    Sheets("Weekly Report - New").Rows("55:56").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("69:70").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("83:84").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("97:98").EntireRow.Hidden = False
            Sheets("Weekly Report - New").Rows("54").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("68").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("82").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("96").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("57:63").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("71:77").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("85:91").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("99:105").EntireRow.Hidden = True
                    Sheets("Weekly Report - New").Rows("30:34").EntireRow.Hidden = False
                    Sheets("Weekly Report - New").Rows("18:29").EntireRow.Hidden = True
                    Sheets("Weekly Report - New").Rows("35:47").EntireRow.Hidden = True

'Unhides 'Barcelona' & 'Madrid' under the major cities section and keeps the rest hidden.
ElseIf Target.Address = ("$D$10") And Target = "Spain" Then
Sheets("Weekly Report - New").Unprotect
    Sheets("Weekly Report - New").Rows("57:58").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("71:72").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("85:86").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("99:100").EntireRow.Hidden = False
            Sheets("Weekly Report - New").Rows("54:56").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("59:63").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("68:70").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("73:77").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("82:84").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("87:91").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("96:98").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("101:105").EntireRow.Hidden = True
                    Sheets("Weekly Report - New").Rows("36:40").EntireRow.Hidden = False
                    Sheets("Weekly Report - New").Rows("18:35").EntireRow.Hidden = True
                    Sheets("Weekly Report - New").Rows("41:47").EntireRow.Hidden = True

'Unhides 'Florence', 'Maples', 'Milan', 'Rome' & 'Venice' under the major cities section and keeps the rest hidden.
ElseIf Target.Address = ("$D$10") And Target = "Italy" Then
Sheets("Weekly Report - New").Unprotect
    Sheets("Weekly Report - New").Rows("59:63").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("73:77").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("87:91").EntireRow.Hidden = False
    Sheets("Weekly Report - New").Rows("101:105").EntireRow.Hidden = False
            Sheets("Weekly Report - New").Rows("54:58").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("68:72").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("82:86").EntireRow.Hidden = True
            Sheets("Weekly Report - New").Rows("96:100").EntireRow.Hidden = True
                    Sheets("Weekly Report - New").Rows("42:46").EntireRow.Hidden = False
                    Sheets("Weekly Report - New").Rows("18:41").EntireRow.Hidden = True
                    Sheets("Weekly Report - New").Rows("47").EntireRow.Hidden = True

End If

Sheets("Weekly Report - New").Rows("108:121").EntireRow.Hidden = True
Sheets("Weekly Report - New").Protect

Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

这对于“每周报告-新建”工作表来说工作得很好(如果有点慢)。我现在需要它对“累积报告-新建”工作表执行相同的操作。

Answer 1

首先，将隐藏和取消隐藏操作分别减少为一个用于true和一个用于false以提高速度。例如

ws.Range("54:54,68:68,82:82,96:96,24:28").EntireRow.Hidden = False
ws.Range("55:63,69:77,83:91,97:105,18:23,29:47").EntireRow.Hidden = True

注意在这里您必须使用Range而不是Rows。

并将你的代码放到一个过程中，这样你就可以在任何工作表上重用它。请注意，您需要调整France、Spain和Italy的代码

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.DisplayAlerts = False 'Prevents general pop-ups
    Application.Calculation = xlManual 'Formulas are not calculated
    Application.ScreenUpdating = False 'What the user see's on screen will not change

    If Intersect(Target, Me.Range("D10")) Is Nothing Then Exit Sub

    ' call the function
    HideAndUnhideRows ThisWorkbook.Worksheets("Weekly Report - New"), Me.Range("D10").Value
    HideAndUnhideRows ThisWorkbook.Worksheets("Cumulative Report - New"), Me.Range("D10").Value

    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

Private Sub HideAndUnhideRows(ws As Worksheet, Criteria As String)
    ws.Unprotect
    Select Case Criteria

    Case "" 'Hides all major city information so just the titles are shown.
        ws.Range("54:63,68:77,82:91,96:105,23:47").EntireRow.Hidden = True
        ws.Range("18:31").EntireRow.Hidden = False

    Case "UK" 'Unhides 'London' under the major cities section and keeps the rest hidden.
        ws.Range("54:54,68:68,82:82,96:96,24:28").EntireRow.Hidden = False
        ws.Range("55:63,69:77,83:91,97:105,18:23,29:47").EntireRow.Hidden = True

    Case "France" 'Unhides 'French Riviera' & 'Paris' under the major cities section and keeps the rest hidden.

    Case "Spain" 'Unhides 'Barcelona' & 'Madrid' under the major cities section and keeps the rest hidden.

    Case "Italy" 'Unhides 'Florence', 'Maples', 'Milan', 'Rome' & 'Venice' under the major cities section and keeps the rest hidden.

    End Select

    ws.Range("108:121").EntireRow.Hidden = True
    ws.Protect
End Sub

Answer 2

因为有一大堆不同的行需要隐藏和取消隐藏，所以我做了第一个例子，你可以用它来做剩下的事情：

Dim wb As Workbook, ws As Worksheet, SheetNames
Application.DisplayAlerts = False 'Prevents general pop-ups
Application.Calculation = xlManual 'Formulas are not calculated
Application.ScreenUpdating = False 'What the user see's on screen will not change

If Intersect(Target, Range("D10")) Is Nothing Then Exit Sub

Set wb = ThisWorkbook
SheetNames = Array("Weekly Report - New", "Cumulative Report - New")


StrValue = Target.Value
Select Case StrValue
    'Hides all major city information so just the titles are shown.
    Case vbNullString
        For i = 0 To 1
            Set ws = wb.Sheets(SheetNames(i))
            With ws
                .Unprotect
                Union(.Rows("54:63"), .Rows("68:77"), .Rows("82:91"), .Rows("96:105"), .Rows("23:47")). _
                    EntireRow.Hidden = True
                .Rows("18:31").EntireRow.Hidden = False
            End With
        Next i

    'All the other cases

End Select

我所做的是为你需要处理的每个工作表创建一个循环(假设两者都有相同的数据)，并使用urdearboy关于联合的说法，以提高可读性和更快的处理速度。

希望这能给你指明正确的方向。如果你还需要帮助，尽管开口。