VBA 代码需要很长时间才能执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13765640/
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 takes very long time to execute
提问by Mehper C. Palavuzlar
The following VBA code takes very long time to execute. I ran it 25 minutes ago for 48,000 rows and it's still running. How can I shorten the execution time?
以下 VBA 代码需要很长时间才能执行。我在 25 分钟前运行了 48,000 行，但它仍在运行。如何缩短执行时间？
Sub delrows()
Dim r, RowCount As Long
r = 2
ActiveSheet.Columns(1).Select
RowCount = UsedRange.Rows.Count
userresponse = MsgBox("You have " & RowCount & " rows", vbOKOnly, "Info")
Rows(RowCount).Delete Shift:=xlUp
' Trim spaces
Columns("A:A").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, searchFormat:=False, _
ReplaceFormat:=False
' Delete surplus columns
Range("L:T,V:AA,AE:AG,AR:AR,AU:AU,AZ:AZ").Select
Selection.Delete Shift:=xlToLeft
' Delete surplus rows
Do
If Left(Cells(r, 1), 1) = "D" _
Or Left(Cells(r, 1), 1) = "H" _
Or Left(Cells(r, 1), 1) = "I" _
Or Left(Cells(r, 1), 2) = "MD" _
Or Left(Cells(r, 1), 2) = "ND" _
Or Left(Cells(r, 1), 3) = "MSF" _
Or Left(Cells(r, 1), 5) = "MSGZZ" _
Or Len(Cells(r, 1)) = 5 _
Or Cells(r, 3) = 0 Then
Rows(r).Delete Shift:=xlUp
ElseIf Int(Right(Cells(r, 1), 4)) > 4000 Then
Rows(r).Delete Shift:=xlUp
Else: r = r + 1
End If
Loop Until (r = RowCount)
End Sub
回答by Scott Holtzman
The biggest issue is probably the amount of data you are looping through. I've updated your code to create a formula to check if the row needs to be deleted, then you can filter on that formula result and delete all rows at once.
最大的问题可能是您循环的数据量。我已经更新了您的代码以创建一个公式来检查是否需要删除该行，然后您可以过滤该公式结果并一次删除所有行。
I've made a bunch of comments to both help you clean your code and understand what I did. I prefaced my comments with '=>.
我已经发表了大量评论，以帮助您清理代码并了解我所做的事情。我以'=>.
One last note, loading the values into an array may help as well, but if you have many, many columns of data, this may be more difficult. I don't have a ton of experience with it, but I know it makes things worlds faster!
最后一个注意事项，将值加载到数组中也可能有所帮助，但如果您有很多很多列数据，这可能会更困难。我对它没有很多经验，但我知道它能让世界变得更快！
Good luck and have fun!
祝好运并玩得开心点！
Option Explicit
Sub delrows()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim r As Long, RowCount As Long
r = 2
Dim wks As Worksheet
Set wks = Sheets(1) '=> change to whatever sheet index (or name) you want
'=> rarely a need to select anything in VBA [ActiveSheet.Columns(1).Select]
With wks
RowCount = .Range("A" & .Rows.Count).End(xlUp).Row '=> as opposed to [RowCount = UsedRange.Rows.Count], as UsedRange can be misleading
'NOTE: this also assumes Col A will have your last data row, can move to another column
userresponse = MsgBox("You have " & RowCount & " rows", vbOKOnly, "Info")
.Rows(RowCount).Delete Shift:=xlUp
' Trim spaces
'=> rarely a need to select anything in VBA [Columns("A:A").Select]
.Range("A1:A" & RowCount).Replace What:=" ", Replacement:="", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, searchFormat:=False, _
ReplaceFormat:=False
' Delete surplus columns
'=> rarely a need to select anything in VBA [Range("L:T,V:AA,AE:AG,AR:AR,AU:AU,AZ:AZ").Select]
.Range("L:T,V:AA,AE:AG,AR:AR,AU:AU,AZ:AZ").Delete Shift:=xlToLeft ' as opposed to Selection.Delete Shift:=xlToLeft
' Delete surplus rows
'=> Now, here is where we help you loop:
'=> First insert column to the right to capture your data
.Columns(1).Insert Shift:=xlToRight
.Range("A1:A" & RowCount).FormulaR1C1 = "=If(OR(Left(RC[1],1) = ""D"",Left(RC[1],1) = ""H"", Left(RC[1],1) = ""I"", Left(RC[1],2) = ""MD"",Left(RC[1],2) = ""ND"",Left(RC[1],3) = ""MSF"",Left(RC[1],5) = ""MSGZZ"",Len(RC[1])=5),""DELETE"",If(Int(Right(RC[1],4)) > 4000,""DELETE"",""""),""""))"
'=> Now, assuming you something to delete ...
If Not .Columns(1).Find("DELETE", LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
'=> filter and delete
.Range("A1:A" & RowCount).AutoFilter 1, "DELETE"
Intersect(.UsedRange, .UsedRange.Offset(1), .Range("A1:A" & RowCount)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End If
'=> Get rid of formula column
.Columns(1).EntireColumn.Delete
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
回答by InContext
the reason its so slow is you are iterating over each cell. Below copies to an array, finds the rows that need deleting and then deletes. Update Sheet4 to your sheet and Range("A2").CurrentRegion to the area you require:
它如此缓慢的原因是您正在迭代每个单元格。下面复制到一个数组，找到需要删除的行然后删除。将 Sheet4 更新到您的工作表和 Range("A2").CurrentRegion 到您需要的区域：
Dim data() As Variant
Dim count As Double, i As Double, z As Double, arrayCount As Double
Dim deleteRowsFinal As Range
Dim deleteRows() As Double
Application.ScreenUpdating = False
data = Sheet4.Range("A2").CurrentRegion.Value2
For i = 1 To UBound(data, 1)
count = count + 1
If (data(i, 1) = "D" Or Left(data(i, 1), 1) = "H" Or Left(data(i, 1), 1) = "I" Or Left(data(i, 1), 2) = "MD" _
Or Left(data(i, 1), 2) = "ND" Or Left(data(i, 1), 3) = "MSF" Or Left(data(i, 1), 5) = "MSGZZ" _
Or Len(data(i, 1)) = 5 Or data(i, 3) = 0 Or Int(Right(IIf(Cells(i, 1) = vbNullString, 0, Cells(i, 1)), 4)) > 4000) Then
ReDim Preserve deleteRows(arrayCount)
deleteRows(UBound(deleteRows)) = count
arrayCount = arrayCount + 1
End If
Next i
Set deleteRowsFinal = Sheet4.Rows(deleteRows(0))
For z = 1 To UBound(deleteRows)
Set deleteRowsFinal = Union(deleteRowsFinal, Sheet4.Rows(deleteRows(z)))
Next z
deleteRowsFinal.Delete Shift:=xlUp
Application.ScreenUpdating = True
回答by shahkalpesh
Turn off the screen updates to start with. Add your observations post the following.
You can disable calculations as well, if you think it isn't affecting anything as such.
关闭屏幕更新开始。在以下帖子中添加您的观察。
您也可以禁用计算，如果您认为它不会影响任何此类内容。
Application.ScreenUpdating = False
your code...
Application.ScreenUpdating = True
EDIT: I have uploaded a file here - https://dl.dropbox.com/u/24702181/TestDeleteRowsInChunk.xls
编辑：我在这里上传了一个文件 - https://dl.dropbox.com/u/24702181/TestDeleteRowsInChunk.xls
The workbook is macro enabled.
After opening, click on "Recover Data" followed by "Start Deleting".
工作簿已启用宏。
打开后，单击“恢复数据”，然后单击“开始删除”。
Take a look at the code for details. I suppose it can be optimized further.
A couple of hints
查看代码了解详细信息。我想它可以进一步优化。
几个提示
- Do a reverse loop.
- Get cell contents in an array, use array to check for values.
- Build a string for rows to be deleted.
- Delete it in chunks.
- 做一个反向循环。
- 获取数组中的单元格内容，使用数组检查值。
- 为要删除的行构建一个字符串。
- 分块删除。
