lukecj
05-03-2010, 08:18 AM
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
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