specialcells method errors when criteria not found
Try it with two range variables:
Sub CleanCancelledChks()
Dim r As Range, Dim r1 as Range
With ActiveSheet
Set r = .Range(.Range("D2"), .Range("D" & _
Rows.Count).End(xlUp))
.Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*"
On Error Resume Next
set r1 = Nothing
Set r1 = r.SpecialCells(xlCellTypeVisible)
On Error goto 0
.AutoFilterMode = False
if not r1 is nothing then _
r1.EntireRow.Delete
End With
End Sub
--
Regards,
Tom Ogilvy
"ms" wrote in message
...
Thank you Tom and K.
There is something not right though.
When testing with a set of records with no negative sign,
r.EntireRow.Delete is
deleting all records from row 2 down.
The "If Not r is nothing then r.EntireRow.Delete" sees the statement as
True and
deletes all records except for the first line.
Thank you,
mark
Tom Ogilvy wrote:
Sub CleanCancelledChks()
Dim r As Range
With ActiveSheet
Set r = .Range(.Range("D2"), .Range("D" & _
Rows.Count).End(xlUp))
.Columns("D:D").AutoFilter Field:=1, Criteria1:="=*-*"
On Error Resume Next
Set r = r.SpecialCells(xlCellTypeVisible)
On Error goto 0
.AutoFilterMode = False
if not rng is nothing then _
r.EntireRow.Delete
End With
End Sub
|