自动筛选 Excel VBA 后删除隐藏/不可见行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22275423/
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 Hidden/Invisible Rows after Autofilter Excel VBA
提问by CaptainABC
I guess this is pretty straight forward, but for some reason it just does not seem to work for me :(
我想这很简单，但由于某种原因，它似乎对我不起作用:(
I have the below code which auto-filters the data based on the criteria that I have specified:
我有以下代码，它根据我指定的标准自动过滤数据：
Dim lastrow As Long
lastrow = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row
With Sheet2
.AutoFilterMode = False
With .Range("A1:AF" & lastrow)
.AutoFilter
.AutoFilter Field:=7, Criteria1:="Yes", Operator:=xlFilterValues
End With
What I am now looking to do is delete all the Unfiltered(Hidden) rows that do not fit the criteria.
我现在要做的是删除所有不符合条件的未过滤（隐藏）行。
I tried so far:
我到目前为止尝试过：
Sub RemoveHiddenRows
Dim oRow As Object
For Each oRow In Sheets("Sheet2").Rows
If oRow.Hidden Then oRow.Delete
Next
End Sub
But the problem with this code is that it would only remove every other row of consecutive hidden rows because the each increments the row considered even when a row has been deleted and all lower rows have moved up one.
但是这段代码的问题在于它只会删除每隔一行的连续隐藏行，因为即使在删除一行并且所有较低的行都向上移动时，每行都会增加所考虑的行。
Also I would prefer something without a loop if it's possible, kind of like the oppositeof .SpecialCells(xlCellTypeVisible).EntireRow.Delete
此外，我会喜欢的东西没有一个循环，如果有可能，一种像对面的.SpecialCells(xlCellTypeVisible).EntireRow.Delete
All help will be highly appreciated.
所有帮助将不胜感激。
回答by Dmitry Pavliv
So I was kind of looking to get rid of Unfiltered Data rather than trying to reverse all the criteria and delete the visible cells
所以我有点想摆脱未过滤的数据，而不是试图反转所有条件并删除可见单元格
I would use this one:
我会用这个：
Sub RemoveHiddenRows()
Dim oRow As Range, rng As Range
Dim myRows As Range
With Sheets("Sheet3")
Set myRows = Intersect(.Range("A:A").EntireRow, .UsedRange)
If myRows Is Nothing Then Exit Sub
End With
For Each oRow In myRows.Columns(1).Cells
If oRow.EntireRow.Hidden Then
If rng Is Nothing Then
Set rng = oRow
Else
Set rng = Union(rng, oRow)
End If
End If
Next
If Not rng Is Nothing Then rng.EntireRow.Delete
End Sub
回答by Qudsia
I used Dmitry Pavliv's solution for my filtered table and it worked (thanks!) but would intermittently give error: "delete method of range class failed" error.
我为我的过滤表使用了 Dmitry Pavliv 的解决方案，它有效（谢谢！）但会间歇性地给出错误：“范围类的删除方法失败”错误。
Error seemed to occur when only one hidden row was to be deleted. It may or may not be of significance that the lone hidden row was right under the table header.
当只删除一个隐藏行时，似乎会发生错误。单独的隐藏行位于表格标题的正下方可能重要，也可能不重要。
Stepping through the code, rng pointed to correct cell, and showed just the single cell. It was probably an issue with using a Table instead of named range, though other hidden rows deleted fine in same table format.
逐步执行代码，rng 指向正确的单元格，并仅显示单个单元格。使用表而不是命名范围可能是一个问题，尽管其他隐藏行以相同的表格式删除得很好。
Macro has been working fine after I modified the last portion of the code from this:
在我修改了代码的最后一部分后，宏一直工作正常：
If Not rng Is Nothing Then rng.EntireRow.Delete
To this:
对此：
If rng.Rows.Count = 1 Then
ws.Rows(rng.Row & ":" & rng.Row).Delete
ElseIf rng Is Nothing Then
rng.EntireRow.Delete
End If
For some reason, deleting that single row in this format works. I'm not quite sure why. The rng object is pointing to the correct cell and I'm using it to get the row number, so not sure why it's not working in rng.entirerow.delete statement. Oh well. Sharing as came across many posts with same error unresolved.
出于某种原因，以这种格式删除那一行是可行的。我不太确定为什么。rng 对象指向正确的单元格，我用它来获取行号，所以不确定为什么它在 rng.entirerow.delete 语句中不起作用。那好吧。分享遇到许多帖子，但未解决相同的错误。
