VBA - 代码执行速度非常慢 [英] VBA - Code Execution is Extremely Slow

问题描述

我有一个相当简单的一些代码，我用来循环使用excel数据表。基本上，我有超过15万行的数据，我想删除每一行，除了那些在其中有仪器故障的行，在这种情况下，我想要那一行和它上面和下面的那一行（可能包含与失败）。
这是我的代码：

  Sub UnsignedFailure（）
 Application.ScreenUpdating = False 
'起始点
设置r =范围（E1）
 
'循环，总行是一个函数，快速执行
对于t = 3到TotalRows 
'检查失败
k = InStr（1，r.Cells（t，1）.Value，Instrument Failure）
'验证它对应于GC 
b = InStr（1，r .Cells（t，1）.Offset（0，-3）.Value，GC）
如果k < 0和b  - 0然后
'如果为true则跳过两行
t = t + 2 
 Else 
'删除上一行，因为此行不是故障
 r.Cells （t  -  1，1）.EntireRow.Delete 
'返回一行以删除行
t = t  -  1 
结束如果
下一步t 
 
 Application.ScreenUpdating = True 
 End Sub 
  

我已经通过逐步通过，它的工作。然而，即使每次IF都是正确的代码中断，中断之间的时间似乎很长（可能需要5分钟来检查20,000个单元格）。如果我让代码运行没有破坏，我还没有得到它完成（电脑冻结）。我的代码缺少某些东西或低效吗？任何帮助将不胜感激！

解决方案

我的excel宏的经验告诉我，复制与您的条件到新的工作表，不删除不匹配的行。

每次删除一行时，重新计算工作表都需要几毫秒（即使没有公式，它仍然检查每个单元格以查看是否需要更新），并将显示更新到用户。您已经禁用屏幕更新，这肯定会减少一段时间。另一种方法是关闭Excel Calculation，将 Application.Calculation = xlCalculationManual 放在子系统的顶部，以禁用重新计算，而 Application.Calculation = xlCalculationAutomatic 在底部启用它。 本网站对Excel的VBA代码优化有一些很好的指导。

正如我所说，我认为最有效的办法是在符合条件的情况下将资料移至新表格。

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:

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!

解决方案

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.

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 = xlCalculationManual at the top of your sub to disable recalculation and Application.Calculation = xlCalculationAutomatic at the bottom to enable it again. This site has some good pointers on optimization of VBA code for excel.

As I said, I think the most efficient thing would be to move data to a new sheet when it matches your conditions.

这篇关于VBA - 代码执行速度非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！