vba 过滤和删除数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27290977/
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
Filter and delete data
提问by Vigneshwaran Kandaswamy
In a Excel sheet I have data from column A1 to G12.
在 Excel 工作表中，我有从 A1 列到 G12 列的数据。
In the C column I have employee ID. I need to filter certain employee ID and delete those rows.
在 C 列中，我有员工 ID。我需要过滤某些员工 ID 并删除这些行。
Example: Filter three employee ID 51168, 79783 and 70682 and delete.
示例：过滤三个员工 ID 51168、79783 和 70682 并删除。
I recorded a macro. If the particular employee ID is not in the place means it is deleting some other row.
我录制了一个宏。如果特定的员工 ID 不在该位置，则意味着它正在删除其他一些行。
ActiveSheet.Range("$A:$I").AutoFilter Field:=3, Criteria1:=Array( _
"51168", "70682", "79783"), Operator:=xlFilterValuesRows("2:2").Select
Range("C2").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("C1").Select
ActiveSheet.Range("$A:$I").AutoFilter Field:=3
回答by Krish
just execute the filter first, select the results and use the "EntireRow" keyword to delete the entire row.
只需先执行过滤器，选择结果并使用“ EntireRow”关键字删除整行。
something like:
就像是：
ActiveSheet.Range("$A:$I").AutoFilter Field:=3, Criteria1:=Array( _
"51168", "70682", "79783"), Operator:=xlFilterValues
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
also its worth making sure you have at least one result before you delete all empty rows for no reason.
在无故删除所有空行之前，确保至少有一个结果也是值得的。
回答by Raugmor
I reckon all rows in between will be deleted too, including hidden; as the list is not that long, you might want try to delete rows with the following trick after filtering
我认为中间的所有行也将被删除，包括隐藏的；由于列表不是那么长，您可能希望在过滤后尝试使用以下技巧删除行
For Each cell In Range("A2", Range("A2").End(xlDown)).SpecialCells(xlCellTypeVisible)
cell.EntireRow.Delete
Next
