Can any one see why in vba a for each doesn't catch all rows that meet the if statement condition? This simple method below will delete a row if it contains cells with a length of 3. It deletes some but not all but will eventually delete all if I keep running it.
Sub delete_3Digit_Numbers()
For Each cell In Selection
If cell.Value <> "" And Len(cell.Value) = 3 Then
Rows(cell.Row).EntireRow.Delete
End If
Next cell
End Sub
Please, try the next way. The code will be faster, anyhow:
Sub delete_3Digit_Numbers()
Dim cell As Range, rngDel As Range
For Each cell In Selection
If cell.Value <> "" And Len(cell.Value) = 3 Then
If rngDel Is Nothing Then
Set rngDel = cell
Else
Set rngDel = Union(rngDel, cell)
End If
End If
Next cell
If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
End Sub
As I said in my above comment, using your way of iteration looses the reference for the following cell after a row deletion. You should iterate backwards, or use a Union range, as the above code does. Anyhow, deleting one row at a time consumes time and for a big range, the code is slow. using the above code, which deletes the rows at the end is obviously faster.
UnionRange and delete its rows at once of the code end. In such a loop, after row deletion, the reference for the next one is lost...