（Excel 2003 VBA）根据列中的多个条件删除整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17562846/
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
(Excel 2003 VBA) Delete entire rows based on multiple conditions in a column
提问by nclfrk799
I have an interesting issue. I've tried searching this site and Google but there are only slightly related problems, none which actually address this specific need.
我有一个有趣的问题。我试过在这个网站和谷歌上搜索，但只有轻微相关的问题，没有一个能真正解决这个特定的需求。
I have a spreadsheet with 10 columns (let's call them A-J). I need to delete all the rows that do NOT have a value of "30", "60", "90", "120"or blank within the cells of column H.
我有一个包含 10 列的电子表格（我们称之为A-J）。我需要删除"30", "60", "90", "120"H 列的单元格中没有值或空白的所有行。
Though there are many ways of doing this, all of them have relied on loops, which doesn't work for me as this dataset has over 25k rows and it takes 10+ minutes to run - too long.
尽管有很多方法可以做到这一点，但所有方法都依赖于循环，这对我不起作用，因为这个数据集有超过 25k 行，运行时间超过 10 分钟 - 太长了。
I've been looking at autofilter options in conjunction with the .Findfunction (e.g. find all rows with H cells that don't meet the criteria and delete) but AutoFilteron 2003 only works with 2 criteria, while I have 5 to check against. I'm not sure how to proceed.
我一直在研究与.Find函数结合使用的自动筛选选项（例如，查找包含不符合条件的 H 单元格的所有行并删除），但AutoFilter在 2003 年仅适用于 2 个条件，而我有 5 个要检查。我不知道如何继续。
Any help is appreciated.
任何帮助表示赞赏。
回答by Tim Williams
This deleted all matching rows (~10%) in a sample of 25k rows in 20sec
这在 20 秒内删除了 25k 行样本中的所有匹配行（~10%）
Sub tt()
Dim rw As Range
Dim all As Range
Dim t
Dim b As Boolean
t = Timer
For Each rw In Range("A1").CurrentRegion.Rows
If rw.Cells(8).Value < 1 Then
If b Then
Set all = Application.Union(rw, all)
Else
Set all = rw
b = True
End If
End If
Next rw
If not all is nothing then all.EntireRow.Delete
Debug.Print "elapsed: " & Timer - t
End Sub
回答by rangan
You can try Advanced Filter option where you can give more than two criteria to filter the list. After filtering the list matching the criteria you set, the filtered list can be copied to another location (option available) and the remaining deleted.
您可以尝试高级筛选选项，您可以在其中提供两个以上的条件来筛选列表。筛选出与您设置的条件匹配的列表后，可以将筛选后的列表复制到另一个位置（可用选项）并删除其余的位置。
回答by d-stroyer
You can add a column with the condition of your own:
您可以添加具有自己条件的列：
=IF(OR(H1=30;H1=60;H1=90;H1=120;H1="");"DELETE";"")
(the formula is given for row 1, you have to copy-paste it to the entire range)
（公式是针对第 1 行给出的，您必须将其复制粘贴到整个范围内）
Then use filtering and sorting to select the rows to delete.
然后使用过滤和排序来选择要删除的行。
回答by LS_???
Some speed tips:
一些速度提示：
- When using large data, assign values to array and use array instead of *.Value;
- When working with full columns, ignore empty columns at bottom;
- When making intensive changes in worksheet, disable screen update and automatic calculation.
- 使用大数据时，给数组赋值，用数组代替*.Value；
- 处理完整列时，忽略底部的空列；
- 在工作表中进行大量更改时，禁用屏幕更新和自动计算。
Stating this, I would use this code:
说明这一点，我会使用这个代码：
Sub Macro1()
Dim su As Boolean, cm As XlCalculation
Dim r As Long, v(), r_offset As Long
su = Application.ScreenUpdating
Application.ScreenUpdating = False 'Disable screen updating
cm = Application.Calculation
Application.Calculation = xlCalculationManual 'Disable automatic calculation
'Only use used values
With Intersect(Range("H:H"), Range("H:H").Worksheet.UsedRange)
v = .Value 'Assign values to array
r_offset = .Row - LBound(v) 'Mapping between array first index and worksheet row number
End With
'Check all row from bottom (so don't need to deal with row number changes after deletion)
For r = UBound(v) To LBound(v) Step -1
Select Case v(r, 1)
Case "30", "60", "90", "120", Empty 'Do nothing
Case Else
Sheet1.Rows(r + r_offset).EntireRow.Delete
End Select
Next
Application.ScreenUpdating = su 'Restore screen updating
Application.Calculation = cm 'Restore calculation mode
End Sub
回答by nclfrk799
Thanks to all who've suggested solutions. In the between time I ended up figuring out a way to do this in <1 second - apparently I myself didn't realise that AutoFiltercould've supported comparison criteria (greater than, less than etc).
感谢所有提出解决方案的人。在此期间，我最终想出了一种在 <1 秒内完成此操作的方法 - 显然我自己没有意识到这AutoFilter可以支持比较标准（大于、小于等）。
Using a series of autofilters I simply filtered for, then deleted all rows that filtered to "<30", "30120".
使用一系列我简单地过滤的自动过滤器，然后删除所有过滤为“<30”、“30120”的行。
Not elegant, but it did the trick.
不优雅，但它做到了。
