specialcells method errors when criteria not found
 

The sub below is used to check the D column for values with a negative sign. If
the negative sign exists I want to delete the entire row. When there are no
records with a negative sign in the D column, it returns a run-time error 1004 -
No cells were found. How do I rewrite this procedure so that the error is not
returned when no cells with a negative are found but still deletes rows when a
value has a negative sign? I understand that some Range object methods require
that the range contain data. If the range does not contain data, the method
fails. But what would be the proper way around this?


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:="=*-*"
Set r = r.SpecialCells(xlCellTypeVisible)
.AutoFilterMode = False
r.EntireRow.Delete
End With
End Sub


Thank you in advance for any advice.
mark

specialcells method errors when criteria not found
 

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

--
Regards,
Tom Ogilvy


"ms" wrote in message
...
The sub below is used to check the D column for values with a negative
sign. If
the negative sign exists I want to delete the entire row. When there are
no
records with a negative sign in the D column, it returns a run-time error
1004 -
No cells were found. How do I rewrite this procedure so that the error is
not
returned when no cells with a negative are found but still deletes rows
when a
value has a negative sign? I understand that some Range object methods
require
that the range contain data. If the range does not contain data, the
method
fails. But what would be the proper way around this?


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:="=*-*"
Set r = r.SpecialCells(xlCellTypeVisible)
.AutoFilterMode = False
r.EntireRow.Delete
End With
End Sub


Thank you in advance for any advice.
mark

specialcells method errors when criteria not found
 

In your specific case, resume to the next line on an error and if it i
error # 1004, exit the sub. If another error occurred (due t
something other than not finding a match, display it and exit th
sub).

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)
.AutoFilterMode = False
If Err.Number = 1004 Then
Exit Sub
Else
Msgbox "Error Occurred: " & Err.Description
Exit Sub
End If
r.EntireRow.Delete
End With
End Sub



--
Message posted from http://www.ExcelForum.com

specialcells method errors when criteria not found
 

Tom's answer is probably better. On Error Goto 0 simply turns th
normal error handling back on.



--
Message posted from http://www.ExcelForum.com

specialcells method errors when criteria not found
 

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

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

specialcells method errors when criteria not found
 

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

specialcells method errors when criteria not found
 

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

specialcells method errors when criteria not found
 

Thanks again all for your help. I was testing this out and it worked fine until
I did a test with only one record. "if not r1 is nothing then _" returned as
True, though there was no negative sign in the D column, and deleted the record.
I am not sure why "Set r1 = r.SpecialCells(xlCellTypeVisible)" no longer is
equal to Nothing.
I can not try Ron's idea where Criteria searches for <0 values rather than "-"
because the negative symbol is at the end of whatever value is in the D column,
(ex. 0045678-).
Any suggestions?

Thanks all,
mark

Tom Ogilvy wrote:
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