使用Excel 2013隐藏行

因此，我尝试使用基于VBA的几种不同条件在Excel 2013中隐藏行：

• 如果该部分的标题为“未使用”，则隐藏该部分。每个部分都是一个命名范围，以使此操作更容易。
• 如果行是“ Cblank”命名范围的一部分，则将其隐藏。
• 现在开始进行困难的部分-如果C.Value =“”和C.Columns（41）.Value =“”对于Range（“ CNonTest”）中的每个单元格，则将它们隐藏。

Range（“ CNonTest”）在Col C中，应检查的额外列是Col AQ。

为了增加难度，我需要每次在8个不同的验证框中的任何1个发生更改时都运行此宏。

以下是我目前拥有的代码：

    Sub CompHide()

    With Sheets("Comparison").Cells
       .EntireRow.Hidden = False

    If Range("C9").Value = "Unused" Then
        Range("CMarket1").EntireRow.Hidden = True
    End If

    If Range("C115").Value = "Unused" Then
        Range("CMarket2").EntireRow.Hidden = True
    End If

    If Range("C221").Value = "Unused" Then
        Range("CMarket3").EntireRow.Hidden = True
    End If

    If Range("C329").Value = "Unused" Then
        Range("CMarket4").EntireRow.Hidden = True
    End If

    If Range("C437").Value = "Unused" Then
        Range("CMarket5").EntireRow.Hidden = True
    End If

    If Range("C545").Value = "Unused" Then
        Range("CMarket6").EntireRow.Hidden = True
    End If

    If Range("C653").Value = "Unused" Then
        Range("CMarket7").EntireRow.Hidden = True
    End If

    If Range("C761").Value = "Unused" Then
        Range("CMarket8").EntireRow.Hidden = True
    End If

    If Range("C869").Value = "Unused" Then
        Range("CMarket9").EntireRow.Hidden = True
    End If

    If Range("C977").Value = "Unused" Then
        Range("CMarket10").EntireRow.Hidden = True
    End If

    For Each C In Range("CNonTest")
        If C.Value = "" And C.Columns(41).Value = "" Then
            C.EntireRow.Hidden = True
        End If
    Next



    Range("CBlank").EntireRow.Hidden = True

    End With
End Sub

然后在工作表上我有以下代码：

    Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Me.Range("A4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("D4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("G4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("K4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("AO4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("AR4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("AU4")) Is Nothing _
    Or _
    Intersect(Target, Me.Range("AY4")) Is Nothing _
    Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True


End Sub

对于工作表代码，我也尝试这样做无济于事

Private Sub Worksheet_Change(ByVal Target As Range)

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

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



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

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



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

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



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

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



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

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



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

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True



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

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True


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

    Application.EnableEvents = False 'to prevent endless loop
    Application.ScreenUpdating = False

    Call CompHide

    Application.ScreenUpdating = True
    Application.EnableEvents = True


End Sub

这段代码似乎都可以正常工作，当我使用F8逐步完成CompHide时，它可以完美运行。所以我认为问题出在表本身的代码上。您会在该代码中看到一条注释，该注释提到要防止无休止的循环，该注释来自某些使我不满意的代码，但我不太清楚它的用途，但根据注释我会留意。

当我更改验证框时，它不再只隐藏其中的一些正确的东西。幸运的是，我还没有看到它隐藏的东西，而这本来不是应该的。我不再赘述，因为起初此代码仅查看第一个验证框，但现在查看的全部为8。