vba 快速删除行的VBA宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15375054/
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 macro to delete rows quickly
提问by scaevity
I have several really large excel data files and I need to go through them all and delete all rows where the value of the cell in column T is 1. Right now my code looks like:
我有几个非常大的 excel 数据文件，我需要遍历它们并删除 T 列中单元格值为 1 的所有行。现在我的代码如下所示：
Sub test()
Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("T5", "T900000")
If cell.Value = 1 Then
cell.EntireRow.Delete
End If
Next cell
End Sub
It seems to be working, but takes forever to run and I'm going to have to do this a bunch of times. Is there a better way of doing this, or some way to optimize what I already have to make it run faster?
它似乎有效，但需要很长时间才能运行，我将不得不多次这样做。有没有更好的方法来做到这一点，或者有什么方法可以优化我已经拥有的东西以使其运行得更快？
回答by Floris
This doesn't work as you think... When you delete rows as you iterate through them, you end up skipping rows. Example: imagine your rows have the numbers 1...10 in column A. You look at the first row and decide to delete it. Now you look at the second row. It has the number 3! You never looked at row 2!!
这不像你想的那样工作......当你在遍历行时删除行，你最终会跳过行。示例：假设您的行在 A 列中包含数字 1...10。您查看第一行并决定将其删除。现在你看第二行。它有数字3！你从没看过第 2 行！！
Better method would be to filter the spreadsheet on your criteria for column T, copy it, paste it I to a new worksheet (with formatting etc).
更好的方法是根据 T 列的条件过滤电子表格，复制它，将其粘贴到新的工作表（带有格式等）。
You can turn on macro recording and do this manually; then you will have the exact VBA code. I am sure that will be much faster.
您可以打开宏录制并手动执行此操作；那么您将拥有确切的 VBA 代码。我相信那会快得多。
Even if you don't do that, if you want to do a for eachwhere you delete things, reverse the order (start at the end and work backwards)
即使你不这样做，如果你想做一个for each你删除东西的地方，颠倒顺序（从最后开始，向后工作）
回答by NickSlash
If you wanted to use a loop, the following should not skip items. I think that @Floris Filter method might be quicker though.
如果您想使用循环，以下内容不应跳过项目。我认为 @Floris Filter 方法可能会更快。
Sub Main()
Dim Row As Long
Dim Sheet As Worksheet
Row = 5
Set Sheet = Worksheets("Sheet1")
Application.ScreenUpdating = False
Do
If Sheet.Cells(Row, 20).Value = 1 Then
Sheet.Rows(Row).Delete xlShiftUp
Else
Row = Row + 1
End If
Loop While Row <= 900000
Application.ScreenUpdating = True
End Sub
UpdateI've toggled Application.ScreenUpdatingaround the loop, which usually speeds stuff like this up a lot!
更新我已经Application.ScreenUpdating在循环中切换了，这通常会大大加快这样的速度！
回答by MosTwanTedT
In case you manage your data like a database and want to delete specific rows out of it and it is possible to filter them, there is a trick to speed up your delete-process. This is very fast in contrast to the simple loop-process:
如果您像管理数据库一样管理数据并希望从中删除特定行并且可以过滤它们，则有一个技巧可以加快您的删除过程。与简单的循环过程相比，这非常快：
I compare the times for different examples (with 4806 rows).
我比较了不同示例的时间（4806 行）。
- Standard loop-deletion: 2:25
- Range-deletion: 0:20
- Filter-deletion: 0:01
- 标准循环删除：2:25
- 范围删除：0:20
- 过滤器删除：0:01
Example: I have data in 'Tabelle5' and want to delete specific rows. The data starts at row 6. Every row in column 1, which begin with "OLD#" should be deleted.
示例：我在“Tabelle5”中有数据并想删除特定行。数据从第 6 行开始。第 1 列中以“OLD#”开头的每一行都应该被删除。
1) Here the standard solution (longest time):
1）这里的标准解决方案（最长的时间）：
Dim i As Integer, counter As Integer
Dim strToRemove As String, strToRemoveRange As String
strToRemove = "OLD#"
strToRemoveRange = ""
counter = 0
With Tabelle5
For i = .UsedRange.Rows.Count To 6 Step -1
If Mid(.Cells(i, 1).value, 1, 4) = strToRemove Then
.Rows(i).Delete Shift:=xlUp
End If
Next i
End With
2) Here the Range solution (middle time):
2）这里的范围解决方案（中间时间）：
Dim i As Integer, counter As Integer
Dim strToRemove As String, strToRemoveRange As String
strToRemove = "OLD#"
strToRemoveRange = ""
counter = 0
With Tabelle5
For i = .UsedRange.Rows.Count To 6 Step -1
If Mid(.Cells(i, 1).value, 1, 4) = strToRemove Then
If strToRemoveRange = "" Then
strToRemoveRange = CStr(i) & ":" & CStr(i)
Else
strToRemoveRange = strToRemoveRange & "," & CStr(i) & ":" & CStr(i)
End If
counter = counter + 1
End If
If counter Mod 25 = 0 Then
If counter > 0 Then
.Range(strToRemoveRange).Delete Shift:=xlUp
strToRemoveRange = ""
counter = 0
End If
End If
Next i
If Len(strToRemoveRange) > 0 Then
'.Range(strToRemoveRange).Delete Shift:=xlUp
End If
End With
3) Filter solution (shortest time):
3）过滤解决方案（最短时间）：
Dim i As Integer, counter As Integer
Dim strToRemove As String, strToRemoveRange As String
strToRemove = "OLD#"
strToRemoveRange = ""
counter = 0
With Tabelle5
For i = .UsedRange.Rows.Count To 6 Step -1
If Mid(.Cells(i, 1).value, 1, 4) = strToRemove Then
.Cells(i, 1).Interior.Color = RGB(0, 255, 0)
counter = counter + 1
End If
Next i
If counter > 0 Then
.Rows("5:5").AutoFilter
.AutoFilter.Sort.SortFields.Clear
.AutoFilter.Sort.SortFields.Add( _
Range("A5"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 255, 0)
.AutoFilter.Sort.Header = xlYes
.AutoFilter.Sort.MatchCase = False
.AutoFilter.Sort.Orientation = xlTopToBottom
.AutoFilter.Sort.SortMethod = xlPinYin
.AutoFilter.Sort.Apply
.Rows("6:" & CStr(counter + 5)).Delete Shift:=xlUp
.Rows("5:5").AutoFilter
End If
End With
Here the green lines will be ordered to the top and a range of the green hits will be deleted as a whole. That's the fastest way I know! :-)
在这里，绿线将被排序到顶部，并且一系列绿色命中将作为一个整体被删除。这是我所知道的最快的方法！:-)
I hope it will help someone!
我希望它会帮助某人！
Best regards Tom
最好的问候汤姆
回答by user3431068
The quickest method I have found is to clear row data (.clear) and then sort. For example, I want to get rid of page breaks that show as " ========= "
我找到的最快的方法是清除行数据 (.clear) 然后排序。例如，我想去掉显示为“ ========== ”的分页符
I=20
Do While i <= lRow3
If Left(Trim(ws3.Cells(i, 1)), 1) = "=" Then
ws3.Range(Rows(i - 7), Rows(i + 2)).Clear
'i = i - 7
'lRow3 = lRow3 - 10
End If
i = i + 1
Loop
Now sort then do an xlUp last row (ws3.Range("A1000000").End(xlUp).Row) etc.
现在排序然后在最后一行执行 xlUp (ws3.Range("A1000000").End(xlUp).Row) 等。
Deleting the rows (in one of my files which is 220,000 rows approx.) takes 3 minutes. Clearing the contents takes under 10 seconds.
删除行（在我的一个文件中，大约有 220,000 行）需要 3 分钟。清除内容需要不到 10 秒的时间。
The problem then becomes how to 'remove' the empty rows if you need to move data from below the rows to one above before this is done. :)
如果您需要在完成之前将数据从行下方移动到上方，那么问题就变成了如何“删除”空行。:)
Cheers, BJ
干杯，BJ
