Deleting rows based on criteria

[mufasa]

HI There

I have the folowing code that search for non numeric values, blank and the number 0 in it. If it finds the value it copies it to a LOG worksheet and deletes the row.

If I run throught the code it does not remove all the 0's, blanks or text(#REF!)

WHat am I doing wrong?

[vba] With Worksheets(CSV)
Last_Row = .Cells(.Rows.Count, "A").End(xlUp).Row
j = 1
For i = 1 To Last_Row
If Not IsNumeric(.Cells(i, 3)) Or _
Not IsNumeric(.Cells(i, 1)) Or _
.Cells(i, 1) = "0" Or _
.Cells(i, 1) = "" Then
.Range("A" & i, "D" & i).Copy
Worksheets("LOG").Range("A" & j).PasteSpecial Paste:=xlPasteValues
j = j + 1
.Rows(i).Delete
End If
Next i
End With[/vba]

[tstav]

Hi mufasa,
try looping through the rows from bottom to top in order not to skip a row after each row.delete takes place
[vba]For i = Last_Row To 1 Step -1[/vba]

[mufasa]

Oops ... thanks 4 the info.

[tstav]

You see, what happens is that when you are on row e.g. 5 and this row is deleted, the rest of the rows get shifted up one place, so now in row 5 you actually have row 6 and as if this were not enough, the code runs to the 'Next' clause of the For...Next loop and moves the row pointer to the next row #7. So you miss checking row #6.
Stepping through the loop from the bottom up eliminates this drawback.