vba 自动筛选后删除空白行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22541846/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-12 02:25:55 来源:igfitidea点击:
Delete Blank Rows after AutoFilter
提问by Justin_DavieHigh
I have the following code in my Excel sheet:
我的 Excel 工作表中有以下代码：
Sub DeleteRows
'Filter CMReport to delete (Blank) rows.
With ActiveSheet
.AutoFilterMode = False
.Range("A1:G1").AutoFilter
.Range("A1:G1").AutoFilter Field:=1, Criteria1:="="
.Range("A1:G1").Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub
It will filter the blank rows but will not delete them... it says that no rows are selected. What am I doing wrong.
它会过滤空白行但不会删除它们......它说没有选择任何行。我究竟做错了什么。
回答by Gary's Student
If all you need to do is to delete rows with blanks in column A, you don't need to filter, just:
如果您需要做的只是删除A列中带有空格的行，则不需要过滤，只需：
Sub DeleteRows()
Range("A:A").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
回答by Dmitry Pavliv
I would do this one:
我会这样做：
Sub DeleteRows()
With ActiveSheet
.AutoFilterMode = False 'remove filter
With .Range("A:G")
.AutoFilter Field:=1, Criteria1:="="
On Error Resume Next ' for the case when there is no visible rows
.Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0
End With
.AutoFilterMode = False 'remove filter
End With
End Sub
