SpecialCells Method selects valid records

[ms]

This procedure is used to delete any records where a negative sign exists
anywhere in a value in the D column. It works fine with multiple records, but
if there is only one record in the file, with or without a negative sign in the
file being formatted, the record is deleted.
If I change the SpecialCells Method to r.SpecialCells(xlCellTypeAllValidation)
it doesn't delete the one record but also does not remove records where the
negative sign exists when multiple records exist.

Here is a sample file with 2 records. One has a negative sign, the other does
not and therefore should not be deleted.
A,1111111111,042804,030171,13174.20
A,1111111111,041904,030221-,4243.30

How can I change this to work in both situations?


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").AutoFilter Field:=1, Criteria1:="=*-*"
On Error Resume Next
'Extra variable is to prevent specialcells method from returning
error1004 when no negative values(ie. cancelled chks) exist.
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


Thanks,

 

[Tom Ogilvy]

Using autofilter is clever, but it always treats the top row in the range as
the header row and it will always be visible.

Also, it will always delete it.

So is row2 a header line or a record. You claim it works with multiple
rows, but I suspect you are always deleteing row 2 regardless of whether it
contains a negative or not.

Assuming row 1 is the header line I would use

Sub CleanCancelledChks()
Dim r As Range
Dim r1 As Range
With ActiveSheet
Set r = .Range(.Range("D1"), .Range("D" & _
Rows.Count).End(xlUp))
.Columns("D").AutoFilter Field:=1, Criteria1:="=*-*"
' step down to row 2
set r = r.offset(1,0).Resize(r.rows.count - 1)
On Error Resume Next
'Extra variable is to prevent specialcells method from returning
error1004 when no negative values(ie. cancelled chks) exist.
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

 

[ms]

Thank you again Tom.
I did have to make a small change or two.
I had to move the "On Error Resume Next" above the r.Offset line to prevent the
1004 error. And the range had to start at "D2" rather than D1. All the valid
lines are rolled up into row 1 so nothing happens.
I tested it with one record and with several and it seems to work. I will test
it further.

Have a good weekend.
Mark

 

[Tom Ogilvy]

I guess I misread/misunderstood the problem you were having.

Sub CleanCancelledChks()
Dim r As Range
Dim r1 As Range
With ActiveSheet
Set r = .Range(.Range("D2"), .Range("D" & _
Rows.Count).End(xlUp))
if r(1).Row = 2 and r.rows.count = 1 then
if instr(r,"-") then _
r.EntireRow.Delete
elseif r(1).Row = 1 then
Exit sub
else
.Columns("D").AutoFilter Field:=1, Criteria1:="=*-*"
' step down to row 2
set r = r.offset(1,0).Resize(r.rows.count - 1)
On Error Resume Next
'Extra variable is to prevent specialcells method from returning
'error1004 when no negative values(ie. cancelled chks) exist.
Set r1 = Nothing
Set r1 = r.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilterMode = False
If Not r1 Is Nothing Then _
r1.EntireRow.Delete
End if
End With
End Sub