Delete Row Skipping

[lukecj]

Hello All. I cooked up the below code...

Sub ForwardSettlementReport()

Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("D:D"), ActiveSheet.UsedRange)
For Each cell In rng
    If (cell.Value) >= 0 _
    Or (cell.Value) = "-" Then
Set del = cell
        del.Offset(0, -3).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Delete Shift:=xlUp
End If
Next cell
End Sub

What it does is find all of the cells in column D that are >= 0 and, than, deletes the row leaving only the rows that have negative numbers in column D. However, it only deletes all the rows with positives after you run the code several times (typically 4-5). I can't figure out why the code ultimately deletes all the rows, but does not detect all of the positive numbers in just one run. Any help is much appreciated. Thanks.

Charles

[mdmackillop]

Change your code to delete the last found value first

e.g.

For i = LastRow to 1 Step -1

[lukecj]

How would that look in the code I've got? Still a VBA rookie...Thanks.

[mdmackillop]

A few concepts in here, but try to follow it!

 
Sub ForwardSettlementReport()
Dim rng As Range, i%
    Set rng = Intersect(Range("D:D"), ActiveSheet.UsedRange)
For i = rng.Cells.Count To 1 Step -1
    If (rng(i).Value) >= 0 _
        Or (rng(i).Value) = "-" Then
            rng(i).Offset(, -3).Resize(, 4).Delete Shift:=xlUp
        End If
    Next
End Sub

[lukecj]

Thanks for the help.