我在下面有這個宏，它根據條件洗掉行。我的問題是，如果沒有任何符合洗掉條件的資料行，則會引發錯誤。如果沒有要洗掉的行，我如何讓它不拋出錯誤？
Public Sub Deletion()
Dim wksData As Worksheet
Dim lngLastRow As Long
Dim rngData As Range
'Set references up-front
Set wksData = ThisWorkbook.Worksheets("Tester")
'Identify the last row and use that info to set up the Range
With wksData
lngLastRow = .Range("V" & .Rows.Count).End(xlUp).Row
Set rngData = .Range("V2:V" & lngLastRow)
End With
Application.DisplayAlerts = False
With rngData
'Apply the Autofilter method to the first column of
'the range, using xlOr to select either
.AutoFilter Field:=22, _
Criteria1:="Delete"
'Delete the visible rows while keeping the header
.Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows.Delete
End With
Application.DisplayAlerts = True
'Turn off the AutoFilter
With wksData
.AutoFilterMode = False
If .FilterMode = True Then
.ShowAllData
End If
End With
'Let the user know the rows have been removed
MsgBox "All Rows were removed. Thank you!"
End Sub
uj5u.com熱心網友回復：
Range.SpecialCells洗掉可見單元格時無需使用。過濾后的范圍參考僅自動回傳可見單元格。
Public Sub Deletion()
Dim rngData As Range
With ThisWorkbook.Worksheets("Tester")
Set rngData = .Range("V2", .Cells(.Rows.Count, "V").End(xlUp))
End With
With rngData.CurrentRegion
.AutoFilter Field:=22, Criteria1:="Delete"
.Offset(1).EntireRow.Delete
rngData.Parent.ShowAllData
End With
End Sub
以下是使用方法Range.SpecialCells：
Sub DeleteVisibleRows(Target As Range)
On Error Resume Next
Set Target = Target.SpecialCells(xlCellTypeVisible)
If Err.Number = 0 Then Target.EntireRow.Delete
On Error GoTo 0
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/440539.html
上一篇：VBA不斷將句點更改為逗號
下一篇：用參考陣列的隨機項替換陣列中的項