为什么一个小的Excel VBA宏运行非常慢 [英] Why is a small Excel VBA Macro is running extremely slow

问题描述

我正在撰写一个简短的宏，以隐藏当前没有当前销售额的所有客户。年销售额位于K列（特别是K10-250）。那些单元格使用vlookup从另一个我们转储数据的选项卡中提取数据。我的问题是，为什么这个宏需要10-15分钟的时间呢？我在另一个电子表格上有一个类似的宏，它只需2-3分钟就可以获得超过1,500行。我已经关闭屏幕更新。我不能想到会加速它的其他任何东西。

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.

   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

推荐答案

您可能希望在隐藏行之前将计算设置为手动？另外你可以摆脱你的情况下的如果语句。尝试这样：

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

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

这篇关于为什么一个小的Excel VBA宏运行非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！