在Autofilter Excel VBA之后删除隐藏/不可见的行 [英] Delete Hidden/Invisible Rows after Autofilter Excel VBA
问题描述
我有以下代码自动过滤基于我指定的条件的数据:
Dim lastrow As Long
lastrow = Sheet2.Cells(Sheet2 .Rows.Count,A)。End(xlUp).Row
With Sheet2
.AutoFilterMode = False
带.Range(A1:AF & lastrow)
.AutoFilter
.AutoFilter字段:= 7,条件1:=是,运算符:= xlFilterValues
结束
我现在要做的是删除所有不符合未过滤的(隐藏)行
我已经尝试了:
Sub RemoveHiddenRows
Dim oRow As Object
对于每个oRow In Sheets(Sheet2)。Rows
如果oRow.Hidden然后oRow.Delete
下一个
End Sub
但是这个代码的问题是它的w只能删除连续隐藏行的每隔一行,因为每行都会增加所考虑的行,即使行已被删除,并且所有较低的行都向上移动一行。
如果可以的话,我更喜欢没有循环的东西,就像的相反 .SpecialCells(xlCellTypeVisible ).EntireRow.Delete
所有的帮助将被高度赞赏。
所以我想要摆脱未过滤的数据,而不是试图颠倒所有的标准和删除可见单元格
我会使用这一个:
Sub RemoveHiddenRows()
Dim oRow As Range,rng As Range
Dim myRows As Range
With Sheets(Sheet3)
设置myRows = Intersect(.Range(A:A)。EntireRow, .UsedRange)
如果myRows没有,然后退出Sub
结束
对于每个oRow在myRows.Columns(1).Cells
如果oRow.EntireRow.Hidden然后
如果rng不是,然后
设置rng = oRow
Else
设置rng =联合(rng,oRow)
结束如果
结束如果
下一个
如果不是rng是没有,然后rng.EntireRow.Delete
End Sub
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 opposite of .SpecialCells(xlCellTypeVisible).EntireRow.Delete
All help will be highly appreciated.
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
这篇关于在Autofilter Excel VBA之后删除隐藏/不可见的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!