本文介绍了为什么一个小的Excel VBA宏运行非常慢的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在撰写一个简短的宏,以隐藏当前没有当前销售额的所有客户。年销售额位于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宏运行非常慢的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-23 22:15