vba 根据列中的条件删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11562093/
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
Delete rows based on condition in a column
提问by Elatesummer
I've searched online and on here but can't find anything which seems to fit the bill and work. I have got some code which works but because the length of the range changes as it deletes rows it doesn't catch all of the rows to delete and so I end up running it several times which isn't great...so I'm now trying the AutoFilter approach as recommended on this thread.
我在网上和这里搜索过，但找不到任何符合要求和工作的东西。我有一些有效的代码，但是因为范围的长度在删除行时会发生变化，所以它不会捕获所有要删除的行，所以我最终运行了几次，这不是很好......所以我我现在正在尝试按照此线程上的建议使用 AutoFilter 方法。
I have a spreadsheet with several columns, one of which is 'cost'. I need to go through the 'cost' column (which can sometimes be column 9, sometimes 10, hence the 'find' bit below) and delete any row where the cost is 0...
我有一个包含几列的电子表格，其中一列是“成本”。我需要查看“成本”列（有时可以是第 9 列，有时是第 10 列，因此是下面的“查找”位）并删除成本为 0 的任何行...
Below is the code I've written so far, any help would be very, very much appreciated!!!!
以下是我到目前为止编写的代码，任何帮助将非常非常感谢！！！！
-- edit 3: some more code changes have been made...new code is below.
-- 编辑 3：进行了更多代码更改...新代码如下。
Sub RemoveRows()
Dim cell As Range
Dim lastRow As Long
Dim lastColumn As Integer
Dim criteraColumn As Integer
Dim criteriaRange As Range
lastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
lastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Rows(1).EntireRow.Select
criteriaColumn = Selection.Find(What:="Cost", After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Column
Set criteriaRange = Range("A1", Cells(lastRow, lastColumn))
criteriaRange.AutoFilter Field:=criteriaColumn, Criteria1:="￡0.00"
AutoFilter.Range.Delete
ActiveSheet.AutoFilterMode = False
End Sub
When I run this, it applies the filter on the correct range and to the correct column. I then get the error message: "Run-time error '424': Object required" which applies to the AutoFilter.Range.Deleteline. When I press 'end' or 'debug' on this and look at the spreadsheet, the filter has been applied to the correct column and the correct option (￡0.00) is checked, but no results are returned (I know I need to do some error handling in case of this situation in future but for now there should be at least 10 lines returned by this filter). If I manually press 'OK' on the filter settings in the spreadsheet (without changing any of them!) my 10 results show up correctly so I'm not sure why they're not showing up when I do this programmatically?
当我运行它时，它会将过滤器应用于正确的范围和正确的列。然后我收到错误消息：“运行时错误'424'：需要对象”，它适用于该AutoFilter.Range.Delete行。当我按下“结束”或“调试”并查看电子表格时，过滤器已应用于正确的列并检查了正确的选项 (￡0.00)，但没有返回任何结果（我知道我需要做将来出现这种情况时会进行一些错误处理，但现在此过滤器应该至少返回 10 行）。如果我在电子表格中的过滤器设置上手动按“确定”（不更改任何一个！）我的 10 个结果显示正确，所以我不确定为什么当我以编程方式执行此操作时它们没有显示？
Happy to supply spreadsheet example if required, this is really confusing me!!
如果需要，很高兴提供电子表格示例，这让我很困惑！！
Spreadsheet can be found here
电子表格可以在这里找到
回答by Jon Kelly
You should be able to use the Excel AutoFilter to delete the rows.
您应该能够使用 Excel 自动筛选来删除行。
Cells.AutoFilter Field:=criteriaColumn, Criteria1:="￡0.00"
ActiveSheet.AutoFilter.Range.Offset(1).Delete
ActiveSheet.ShowAllData
This will filter for cost = 0 and then delete all the rows that match the filter.
这将过滤 cost = 0，然后删除所有与过滤器匹配的行。
The operater is automatically equals so you don't need "=￡0.00" as the parameter.
运算符自动等于，因此您不需要“=￡0.00”作为参数。
I added in the ActiveSheet for AutoFilter to get rid of the object not found, and the Offset makes it not delete headers.
我在 ActiveSheet 中添加了 AutoFilter 以摆脱未找到的对象，而 Offset 使其不会删除标题。
回答by Trace
I would do it in another way, for performance reasons (reading + deleting line by line is slow). I answered a similar question before, I think it is also quite applicable for your post.
出于性能原因，我会以另一种方式执行此操作（逐行读取 + 删除很慢）。我之前回答过一个类似的问题，我认为它也很适用于您的帖子。
What I would suggest that you could do:
我建议你可以这样做：
- Instead of reading the sheet row by row, load the entire range into an array once;
- Instead of deleting row by row, transport the values you need into a second array in memory;
- When you have the data you need, apply a oRange.clearcontents to clear the current range;
- Display the new array of data into the sheet by applying oRange = vArray.
- 不是逐行读取工作表，而是将整个范围加载到数组中一次；
- 不是逐行删除，而是将您需要的值传输到内存中的第二个数组中；
- 当你有你需要的数据时，应用 oRange.clearcontents 来清除当前范围；
- 通过应用 oRange = vArray 将新的数据数组显示到工作表中。
Reading / transforming data are performed in memory, and read and write are only performed once.
读取/转换数据在内存中进行，读写只进行一次。
In this thread, you have the code I wrote before: Execution of VBA code gets slow after many iterations
在这个线程中，你有我之前写的代码： 多次迭代后执行 VBA 代码变慢
Let me know if this could help you out, or if you wish to receive more concrete info / code.
让我知道这是否可以帮助您，或者您是否希望收到更具体的信息/代码。
回答by Scott Holtzman
What you need to do is to step backward through the range, since deleting rows automatically moves rows up one by default in Excel.
您需要做的是在该范围内后退一步，因为在 Excel 中，默认情况下删除行会自动将行向上移动一位。
Replace:
代替：
For Each cell In criteriaRange.Cells
If cell.Value = 0 Then
cell.EntireRow.Delete
End If
Next cell
With:
和：
Dim i As Integer
For i = criteriaRange.Rows.Count To 1 Step -1
If criteriaRange.Cells(i) = 0 Then
criteriaRange.Cells(i).EntireRow.Delete
End If
Next
Also, ScreenUpdatinghas no effect on the methods of a macro. It basically turns off "screen-flickering" for every action the macro takes, making it run faster and smoother for the user experience.
此外，ScreenUpdating对宏的方法没有影响。它基本上会为宏执行的每个操作关闭“屏幕闪烁”，使其运行更快，用户体验更流畅。
回答by jrad
You could have it so whenever it deletes a row, it either goes back to the top of the column or you could subtract one from the count. Either way, it wouldn't skip any rows. I'd provide actual code but I'm new with VBA and wouldn't quite know how to do that.
你可以拥有它，所以每当它删除一行时，它要么回到列的顶部，要么你可以从计数中减去一。无论哪种方式，它都不会跳过任何行。我会提供实际代码，但我是 VBA 的新手，不太知道该怎么做。
