You are correct that the criteria supplied by the OP doesn't appear to
work - but since he implied that it did, I didn't check that. Thanks for
the correction.

--
Regards,
Tom Ogilvy



"Ron de Bruin" wrote in message
...
Not working for me

Add a zero in the D column and use this one

Sub CleanCancelledChks2()
Dim r As Range, r1 As Range
With ActiveSheet
Set r = .Range(.Range("D2"), .Range("D" & _
Rows.Count).End(xlUp))
.Columns("D:D").AutoFilter Field:=1, Criteria1:="<0"
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 Ron de Bruin
http://www.rondebruin.nl


"Tom Ogilvy" wrote in message
...
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