英文:
Delete all rows in filtered range Except first filtered row in 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:
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
英文:
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
答案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 ' improve!
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
    
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion ' has headers
    Dim drg As Range: Set drg = rg.Resize(rg.Rows.Count - 1).Offset(1) ' no hdrs.
    
    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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。




评论