PDA

View Full Version : Solved: Delete Row Skipping



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

mdmackillop
05-03-2010, 08:22 AM
Change your code to delete the last found value first

e.g. For i = LastRow to 1 Step -1

lukecj
05-03-2010, 08:27 AM
How would that look in the code I've got? Still a VBA rookie...Thanks.

mdmackillop
05-03-2010, 08:30 AM
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
05-03-2010, 01:34 PM
Thanks for the help.