Excel vba Deleting a row if a cell contains a text

Question

I'm using this code:

Dim r As Integer   
For r = 1 To Sheet1.UsedRange.Rows.Count        
    If Cells(r, "Q") = "0" Then           
    Sheet1.Rows(r).EntireRow.Delete    
    End If
Next

The problem is it doesn't delete everything, it just deletes some of the rows and i have to press the button that uses this code multiple times to delete all instances of "0" in "Q"

Answers from similar questions

How to reverse a For loop
Delete Row based on Search Key VBA

That is because you are trying to delete the rows, which can change the value of rows.count. Try doing the loop in reverse. — shahkalpesh, Feb 02 '14 at 19:25
@shahkalpesh you are correct but shouldn't you post it as an answer and show the step to process from the bottom up. — PyNEwbie, Feb 02 '14 at 19:29
Yeah i got the idea of that it makes sense, but im not sure what do you mean by doing the loop in reverse or how to do it. — user3249749, Feb 02 '14 at 19:42
@user3249749, see [this question](http://stackoverflow.com/questions/17944074/how-to-reverse-a-for-loop) — Dmitry Pavliv, Feb 02 '14 at 19:47
@PyNEwbie: Doing the loop in reverse should be straight forward (with a search on SO as well). Lets close this question as the problem at hand is solved. — shahkalpesh, Feb 03 '14 at 08:26
@user3249749 Don't use a loop - use `AutoFilter`. http://stackoverflow.com/questions/13614997/delete-row-based-on-search-key-vba — brettdj, Feb 03 '14 at 09:08
@shahkalpesh why are you badgering the user to *close* the question? — brettdj, Feb 03 '14 at 09:09
@brettdj: Because the comment by user shows that a solution from another question has worked. — shahkalpesh, Feb 03 '14 at 10:31

score 7 · Accepted Answer · answered Feb 03 '14 at 13:24

I appreciate this has been addressed in the comments, but for completeness, you need to reverse the order of the for/next loop as shown below:

Dim r As Integer   
For r = Sheet1.UsedRange.Rows.Count to 1 step -1
    If Cells(r, "Q") = "0" Then           
        Sheet1.Rows(r).EntireRow.Delete    
    End If
Next

The way you had it previously, by deleting an entire row at the start of the list, you were moving all the content up one row. By starting form the bottom up, deleting an entire row doesn't impact the rows numbers for the items you haven't processed yet.

Glad you got it sorted.

Excel vba Deleting a row if a cell contains a text

1 Answers1