为什么小型 Excel VBA 宏运行速度极慢

Why is a small Excel VBA Macro is running extremely slow

提问by B-Rell

I am writing a short macro to hide all customers that have no current sales for the current year. The YTD sales are in the K column (specifically K10-250). Those cells use a vlookup to pull data from another tab where we dump data. My question is why on earth would this macro take 10-15minutes to run? I have a similar macro on another spreadsheet that takes only 2-3 minutes for over 1,500 rows. I have already turned off screen updating. I can't think of anything else that would speed it up.

我正在编写一个简短的宏来隐藏当年没有当前销售额的所有客户。YTD 销售额在 K 列中（特别是 K10-250）。这些单元格使用 vlookup 从我们转储数据的另一个选项卡中提取数据。我的问题是为什么这个宏需要 10-15 分钟才能运行？我在另一个电子表格上有一个类似的宏，超过 1,500 行只需要 2-3 分钟。我已经关闭了屏幕更新。我想不出还有什么可以加快它的速度。

   Sub HideNoSlackers()
'
' HideNoSlackers Macro
'

'
Application.ScreenUpdating = False
'
 Sheets("CONSOLIDATED DATA").Select
 Dim cell As Range
 For Each cell In Range("K10:K250")
   If cell.Value = 0 Then
     cell.EntireRow.Hidden = True
   Else
     cell.EntireRow.Hidden = False
   End If
 Next
End Sub

采纳答案by PatricK

You might want the calculation to be set Manual before hiding the rows? Also you can get rid of Ifstatements in your case. Try this:

您可能希望在隐藏行之前手动设置计算？你也可以摆脱If你的情况下的陈述。尝试这个：

Sub HideNoSlackers()
    Dim cell As Range, lCalcState As Long

    Application.ScreenUpdating = False
    ' Record the original Calculation state and set it to Manual
    lCalcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    For Each cell In ThisWorkbook.Worksheets("CONSOLIDATED DATA").Range("K10:K250")
        cell.EntireRow.Hidden = (cell.Value = 0)
    Next
    ' Restore the original Calculation state
    Application.Calculation = lCalcState
    Application.ScreenUpdating = True ' Don't forget set ScreenUpdating back to True!
End Sub

回答by Tim Williams

Sub HideNoSlackers()
 Dim cell As Range, rng As Range, rngHide As Range

    Set rng = Sheets("CONSOLIDATED DATA").Range("K10:K250")
    rng.EntireRow.Hidden = False

    For Each cell In rng.Cells
        If cell.Value = 0 Then
            If Not rngHide Is Nothing Then
                Set rngHide = Application.Union(rngHide, cell)
            Else
                Set rngHide = cell
            End If
        End If
    Next

    If Not rngHide Is Nothing Then rngHide.EntireRow.Hidden = True
End Sub

回答by Dale M

Why are you doing this with a macro?

你为什么用宏来做这个？

If you create a table over the data, you can set up a filter on the sales column that will show only those where sales<> 0.

如果您在数据上创建一个表，您可以在 sales 列上设置一个过滤器，该过滤器将只显示那些 sales<> 0 的列。

Macros are useful in excel but the majority of actions that people turn to macros for can be done natively in excel.

宏在 excel 中很有用，但人们求助于宏的大多数操作都可以在 excel 中本地完成。

回答by Dale M

there must be something else that's wrong. Try without .Selecting the sheet but that's not a huge improvement

一定有其他问题。尝试不选择工作表，但这并不是一个巨大的改进

Note rows are visible by default so the Else statement should be optional really.

注意行默认是可见的，所以 Else 语句应该是可选的。

Sub HideNoSlackers()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

    Sheets("CONSOLIDATED DATA").Cells.EntireRow.Hidden = False
    Dim cell As Range
    For Each cell In Sheets("CONSOLIDATED DATA").Range("K10:K250")
        If cell.Value = 0 Then cell.EntireRow.Hidden = True
    Next

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

回答by Max

the shortest code to achieve the same Goal in a very different way:

以非常不同的方式实现相同目标的最短代码：

Sub column_K_not_NULL
    Sheets("CONSOLIDATED DATA").Select
    If ActiveSheet.FilterMode Then Selection.AutoFilter 'if an autofilter already exists this is removed
    ActiveSheet.Range("$K:$K0").AutoFilter Field:=1, Criteria1:="<>0"
End Sub

of course you could put in the standard minimums like

当然，您可以输入标准最小值，例如

application.calculation = Manual 
Application.ScreenUpdating = False

and other way round at the end. Max

并以其他方式结束。最大限度

回答by user1842924

Try disabling page breaks. I had a similar problem that would happen after someone printed from the sheet. This turned on page breaks, and subsequent runs of the script would take forever.

尝试禁用分页符。我有一个类似的问题，在有人从工作表打印后会发生。这打开了分页符，脚本的后续运行将永远持续下去。

ActiveSheet.DisplayPageBreaks = False

回答by user7398891

We found out, that the program Syncplicity in the Version 4.1.0.1533 slows down macros up to 15times slower because events trigger syncplicity.

我们发现，版本 4.1.0.1533 中的程序 Syncplicity 将宏减慢了 15 倍，因为事件会触发同步。

with

和

Application.EnableEvents = False

Application.EnableEvents = False

;do your job here

;在这里做你的工作

Application.EnableEvents = True

Application.EnableEvents = True

the speed is back.

速度又回来了。