VBA：如何删除 Excel 中的筛选行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17194394/
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: How to delete filtered rows in Excel?
提问by mbigun
I have an Excel table that contains some data. By using next vba code I'm trying to filter only blank cells in some fields and delete these rows
我有一个包含一些数据的 Excel 表格。通过使用下一个 vba 代码，我试图仅过滤某些字段中的空白单元格并删除这些行
ActiveSheet.Range("$A:$I$" & lines).AutoFilter Field:=7, Criteria1:= _
"="
ActiveSheet.Range("$A:$I$" & lines).AutoFilter Field:=8, Criteria1:= _
"="
ActiveSheet.Range("$A:$I$" & lines).AutoFilter Field:=9, Criteria1:= _
"="
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.rows.Count - 1).rows.Delete
ActiveSheet.ShowAllData
It works only if I have blank cells in this columns. But I faced with a problem, when I do not have blank cells, and by using above code all my range is removing from the sheet. How to avoid this issue? Should I change my filter condition or something else?
仅当我在此列中有空白单元格时才有效。但是我遇到了一个问题，当我没有空白单元格时，通过使用上面的代码，我的所有范围都从工作表中删除了。如何避免这个问题？我应该改变我的过滤条件还是其他什么？
回答by Jon Crowell
Use SpecialCells to delete only the rows that are visible after autofiltering:
使用 SpecialCells 仅删除自动过滤后可见的行：
ActiveSheet.Range("$A:$I$" & lines).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
If you have a header row in your range that you don't want to delete, add an offset to the range to exclude it:
如果您的范围中有不想删除的标题行，请向该范围添加偏移量以将其排除：
ActiveSheet.Range("$A:$I$" & lines).Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
回答by KeyLimePy
As an alternative to using UsedRange or providing an explicit range address, the AutoFilter.Range property can also specify the affected range.
作为使用 UsedRange 或提供显式范围地址的替代方法，AutoFilter.Range 属性还可以指定受影响的范围。
ActiveSheet.AutoFilter.Range.Offset(1,0).Rows.SpecialCells(xlCellTypeVisible).Delete(xlShiftUp)
As used here, Offset causes the first row after the AutoFilter range to also be deleted. In order to avoid that, I would try using .Resize() after .Offset().
此处使用的 Offset 会导致 AutoFilter 范围之后的第一行也被删除。为了避免这种情况，我会尝试在 .Offset() 之后使用 .Resize()。
