英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论