specialcells method errors when criteria not found

  • Thread starter Thread starter ms
  • Start date Start date
M

ms

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
 
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
 
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
 
Tom's answer is probably better. On Error Goto 0 simply turns th
normal error handling back on.
 
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
 
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
 
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
 
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.
 
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
 
Back
Top