VBA - 代码执行速度极慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16173294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA - Code Execution is Extremely Slow
提问by Gaussian Blur
I've got a fairly simple bit of code I'm using to loop through an excel sheet of data. Basically, I have over 150,000 rows of data and I want to delete every row except ones that have "Instrument Failure" in them, in which case I want that row and the one immediately above and below it (which may contain details relating to the failure). Here's my code:
我有一段相当简单的代码，用于遍历 Excel 数据表。基本上，我有超过 150,000 行数据，我想删除每一行，除了那些有“仪器故障”的行，在这种情况下，我想要该行以及它的正上方和下方的行（其中可能包含与失败）。这是我的代码：
Sub UnsignedFailure()
Application.ScreenUpdating = False
'Start point
Set r = Range("E1")
'Loop, total rows is a function, executes quickly
For t = 3 To TotalRows
'Check for a failure
k = InStr(1, r.Cells(t, 1).Value, "Instrument Failure")
'Verify that it corresponds to a GC
b = InStr(1, r.Cells(t, 1).Offset(0, -3).Value, "GC")
If k <> 0 And b <> 0 Then
'Skip two rows if it is true
t = t + 2
Else
'Delete the previous row since this row is not a failure
r.Cells(t - 1, 1).EntireRow.Delete
'Go back a row to account for the deleted row
t = t - 1
End If
Next t
Application.ScreenUpdating = True
End Sub
I've checked this by stepping through and it works. However, even having the code break every time the "IF" is true, the time between breaks seems very long (maybe 5 min to check 20,000 cells). If I let the code run without breaking, I have yet to get it to complete (computer freezes). Am I missing something or being inefficient in my code? Any help would be greatly appreciated!
我已经通过逐步检查了这一点并且它有效。然而，即使每次“IF”为真时都会中断代码，中断之间的时间似乎很长（检查 20,000 个单元格可能需要 5 分钟）。如果我让代码在不中断的情况下运行，我还没有完成它（计算机死机）。我的代码是否遗漏了什么或效率低下？任何帮助将不胜感激！
回答by Pete Garafano
My experience with excel macros has taught me it is much more efficient to copy data matching your conditions to a new sheet, not deleting rows that don't match.
我使用 excel 宏的经验告诉我，将符合条件的数据复制到新工作表而不是删除不匹配的行，效率要高得多。
Every time you delete a row, it takes excel a few milliseconds to recalculate the sheet (even if there are no formulas, it still checks every cell to see if it needs updating) and update the display to the user. You already disable screen updating, which will definitely cut some of this time off. Another way is to turn off Excel Calculation, put Application.Calculation = xlCalculationManualat the top of your sub to disable recalculation and Application.Calculation = xlCalculationAutomaticat the bottom to enable it again. This sitehas some good pointers on optimization of VBA code for excel.
每次删除一行，excel都需要几毫秒的时间来重新计算工作表（即使没有公式，它仍然会检查每个单元格以查看是否需要更新）并将显示更新给用户。您已经禁用了屏幕更新，这肯定会减少一些时间。另一种方法是关闭 Excel 计算，将其置于Application.Calculation = xlCalculationManual子顶部以禁用重新计算，Application.Calculation = xlCalculationAutomatic并置于底部以再次启用它。 这个站点有一些关于excel的VBA代码优化的很好的指针。
As I said, I think the most efficient thing would be to move data to a new sheet when it matches your conditions.
正如我所说，我认为最有效的方法是在符合您的条件时将数据移动到新工作表。
