时间戳

首页 > 解决方案 > 隐藏和取消隐藏切换按钮 - 运行速度很慢

excel - 隐藏和取消隐藏切换按钮 - 运行速度很慢

问题描述

我有一个包含 12 张工作表的工作簿，并且我在每张工作表中放置了一个命令按钮来隐藏/取消隐藏行。为了隐藏特定的行，我在 A 列中为需要隐藏的每一行输入了“A”。所以代码可以工作，但它会永远运行，需要很长时间并且隐藏或取消隐藏行非常慢。在某些工作表中，要检查的总行数为 100，而某些工作表为 750。请告知运行缓慢的原因，或者是否有更好的方法可以使其运行得更快。这是代码： -

Private Sub CommandButton1_Click()
Sheet2.Unprotect ("aaa")
Dim rng As Range
Dim iRow As Range
Dim hidden_status As Boolean

CommandButton1.Caption = "Show / Hide Guidelines"

On Error Resume Next

   Set rng = Range("A1:A750")
    For Each iRow In rng.Rows
        If iRow.Text = "A" Then
           With iRow.EntireRow
                 hidden_status = .Hidden
                .Hidden = Not hidden_status
           End With
        End If
    Next iRow

On Error GoTo 0
Sheet2.Protect ("aaa")
End Sub

标签： excelvba

解决方案

Each time you hide a row, Excel then is stopping to update your screen (and potentially perform calculations). So to help really speed things up, disable all the screen updates and application events (including calculations) until you've finished hiding.

So with a Sub like this:

Private Sub SetUpdates(ByVal newState As Boolean)
    With Application
        .ScreenUpdating = newState
        .EnableEvents = newState
        .DisplayAlerts = newState
    End With
End Sub

You can do something like this:

Private Sub CommandButton1_Click()
    SetUpdates newState:=False

    '--- hide your rows here

    SetUpdates newState:=True
End Sub

推荐阅读