在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屋！