有隐藏行的工作表表现得很慢

Question

由于某种原因，无论何时宏运行，这位女士的表现都非常缓慢。这正在成为问题，因为每次我试图改变信息的未隐藏的单元格，不属于范围，它仍然运行一个更新，并花费近5-10秒来完成。

要使这一问题复杂化，需要对公式进行哪些修改？

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim c As Range


    For Each c In Range("A7:A98")
        If c.Value = 0 And c.Value = vbNullString Then
        c.EntireRow.Hidden = True
        End If
    Next c

    For Each c In Range("A7:A98")
        If c.Value <> 0 And c.Value <> vbNullString Then
        c.EntireRow.Hidden = False
        End If
    Next c

End Sub

Answer 1

您的逻辑看起来很粗略，很难判断您想要做什么，但是您的逻辑可以被缩短并用于确定布尔.Hidden。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A7:A98")) Is Nothing Then
        On Error GoTo safe_exit
        Application.EnableEvents = False
        Dim trgt As Range
        For Each trgt In Intersect(Target, Range("A7:A98"))
            trgt.EntireRow.Hidden = CBool(trgt.Value = vbNullString)
        Next trgt
    End If

safe_exit:
    Application.EnableEvents = True

End Sub

Answer 2

像这样的东西应该对你有用：

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rCheck As Range
    Dim rCell As Range
    Dim rHide As Range
    Dim lCalc As XlCalculation

    Set rCheck = Me.Range("A7:A98")

    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    On Error GoTo CleanExit

    If Not Intersect(Target, rCheck) Is Nothing Then
        rCheck.EntireRow.Hidden = False
        For Each rCell In rCheck
            If rCell.Value = 0 And rCell.Value = vbNullString Then
                If rHide Is Nothing Then
                    Set rHide = rCell
                Else
                    Set rHide = Union(rHide, rCell)
                End If
            End If
        Next rCell
    End If

    If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True

CleanExit:
    With Application
        .Calculation = lCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub