删除Excel VBA中筛选范围中的所有行,除了第一行。

huangapple go评论108阅读模式
英文:

Delete all rows in filtered range Except first filtered row in excel VBA

问题

我想删除筛选范围内的所有行,除了标题之后的第一行可见行。

例如,

这是一个示例表格:

删除Excel VBA中筛选范围中的所有行,除了第一行。

删除Excel VBA中筛选范围中的所有行,除了第一行。

我想删除所有筛选的苹果行,除了编号为3的行,这是第一个可见的筛选行。

我尝试了下面的代码:

Sub Filter()
    Dim cl, rng As Range

    Range("A1").AutoFilter Field:=1, Criteria1:="Apple"
    Set rng = Range("A2:A7")
    For Each cl In rng.SpecialCells(xlCellTypeVisible)
        cl.EntireRow.Delete     
    Next cl
End Sub

这段代码的问题是它删除了所有筛选的行。如何指定不删除第一个可见行

英文:

I want to delete all rows in filtered range except the first visible row after header.

For example,

This is a sample table:

删除Excel VBA中筛选范围中的所有行,除了第一行。

删除Excel VBA中筛选范围中的所有行,除了第一行。

I want to delete all the filtered rows of apple Except row number 3 which is the first visible filtered row.

I have tried below code :

Sub Filter()
    Dim cl, rng As Range

    Range("A1").AutoFilter Field:=1, Criteria1:="Apple"
    Set rng = Range("A2:A7")
    For Each cl In rng.SpecialCells(xlCellTypeVisible)
        cl.EntireRow.Delete     
    Next cl
End Sub

The problem with this code is that it deletes all the filtered rows. How to specify not to delete first visible row

答案1

得分: 1

使用一个标志来省略第一行

Sub Filter()
    Dim cl As Range, rng As Range ' 声明所有变量的类型,否则它们将成为变量
    Dim FirstRow As Boolean

    FirstRow = True
    Range("A1").AutoFilter Field:=1, Criteria1:="Apple"
    Set rng = Range("A2:A7")
    For Each cl In rng.SpecialCells(xlCellTypeVisible)
        If Not FirstRow Then
            cl.EntireRow.Delete
        End If
        FirstRow = False
    Next cl
End Sub
英文:

Use a flag to omit first row

Sub Filter()
    Dim cl as Range, rng As Range ' type all variables, otherwise they'll be Variants
    Dim FirstRow as Boolean

    FirstRow  = True
    Range("A1").AutoFilter Field:=1, Criteria1:="Apple"
    Set rng = Range("A2:A7")
    For Each cl In rng.SpecialCells(xlCellTypeVisible)
        If Not FirstRow Then
            cl.EntireRow.Delete
        End If
        FirstRow = False
    Next cl
End Sub

答案2

得分: 1

不需要循环。以下是一个示例:

Option Explicit

Sub Filter()
    Dim ws As Worksheet
    Dim rng As Range
    Dim rngFiltered As Range
    
    '~~> 更改为相关的工作表
    Set ws = Sheet1
    
    With ws
        '~~> 移除现有的筛选
        .AutoFilterMode = False
        
        Set rng = ws.Range("A1:A7")
    
        With rng
            .AutoFilter Field:=1, Criteria1:="Grapes"
            
            '~~> 检查第二行是否隐藏
            If ws.Rows(.Offset(1, 0).Row).EntireRow.Hidden = True Then
                If .SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then _
                .Offset(.SpecialCells(xlCellTypeVisible).Areas(2).Row + 1, 0).EntireRow.Delete
            Else
                .Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
        End With
        
        '.AutoFilterMode = False
    End With
End Sub

删除Excel VBA中筛选范围中的所有行,除了第一行。

英文:

No need for a loop.

Here is an example

Option Explicit

Sub Filter()
    Dim ws As Worksheet
    Dim rng As Range
    Dim rngFiltered As Range
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    
    With ws
        '~~> Remove existing filter
        .AutoFilterMode = False
        
        Set rng = ws.Range("A1:A7")
    
        With rng
            .AutoFilter Field:=1, Criteria1:="Grapes"
            
            '~~> Check if the 2nd row is hidden
            If ws.Rows(.Offset(1, 0).Row).EntireRow.Hidden = True Then
                If .SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then _
                .Offset(.SpecialCells(xlCellTypeVisible).Areas(2).Row + 1, 0).EntireRow.Delete
            Else
                .Offset(2, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
        End With
        
        '.AutoFilterMode = False
    End With
End Sub

删除Excel VBA中筛选范围中的所有行,除了第一行。

答案3

得分: 1

删除筛选的行但跳过第一行。

Sub DeleteFilteredSkipFirst()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' 改进!
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion ' 包含标题
    Dim drg As Range: Set drg = rg.Resize(rg.Rows.Count - 1).Offset(1) ' 无标题。
    
    rg.AutoFilter Field:=1, Criteria1:="Apple"
    
    Dim vrg As Range
    On Error Resume Next
        Set vrg = drg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ws.AutoFilterMode = False
    
    If vrg Is Nothing Then Exit Sub
    
    Dim urg As Range, rrg As Range, IsFirstFound As Boolean
    
    For Each rrg In vrg.Rows
        If IsFirstFound Then
            If urg Is Nothing Then
                Set urg = rrg
            Else
                Set urg = Union(urg, rrg)
            End If
        Else
            IsFirstFound = True
        End If
    Next rrg
                
    If urg Is Nothing Then Exit Sub
    
    urg.Delete xlShiftUp
    
    MsgBox "Rows deleted.", vbInformation
    
End Sub
英文:

Delete Filtered Rows But Skip First

<!-- language: lang-vb -->

Sub DeleteFilteredSkipFirst()
    
    Dim ws As Worksheet: Set ws = ActiveSheet &#39; improve!
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    Dim rg As Range: Set rg = ws.Range(&quot;A1&quot;).CurrentRegion &#39; has headers
    Dim drg As Range: Set drg = rg.Resize(rg.Rows.Count - 1).Offset(1) &#39; no hdrs.
    
    rg.AutoFilter Field:=1, Criteria1:=&quot;Apple&quot;
    
    Dim vrg As Range
    On Error Resume Next
        Set vrg = drg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ws.AutoFilterMode = False
    
    If vrg Is Nothing Then Exit Sub
    
    Dim urg As Range, rrg As Range, IsFirstFound As Boolean
    
    For Each rrg In vrg.Rows
        If IsFirstFound Then
            If urg Is Nothing Then
                Set urg = rrg
            Else
                Set urg = Union(urg, rrg)
            End If
        Else
            IsFirstFound = True
        End If
    Next rrg
                
    If urg Is Nothing Then Exit Sub
    
    urg.Delete xlShiftUp
    
    MsgBox &quot;Rows deleted.&quot;, vbInformation
    
End Sub

huangapple
  • 本文由 发表于 2023年1月6日 12:47:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/75027021.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定