CODEWITHSUNDEEP
vbaexcel
click here
click here

Reputation: 836

Deleting row based off cell content with loop

I have the following: 

For i = 1 To 400:
    If Cells(i, 7) = "Client" Then
    Cells(i, 1).EntireRow.Delete
    End If
Next i

The problem is say that I have Client in both Cells(10,7) and Cells(11,7). Once row(10) is deleted row(11) becomes the new row (10) and I'm on my way to looking for Client in row(11) and I won't find it (unless client was in row(12) from the start) so I end up with a lot of rows not deleted.

Upvotes: 0

Views: 48

Answers (2)

Caique Castro Soares da Silva
Caique Castro Soares da Silva

Reputation: 83

If you decrement the variable i inside the if condition, does it work.

Example:

For i = 1 To 400
    If Cells(i, 7) = "Client" Then
        Cells(i, 1).EntireRow.Delete
        i = i – 1
    End If
Next i

Upvotes: 0

Sixthsense
Sixthsense

Reputation: 1971

Inserting or deleting rows in excel sheet should be done from the last line.

For i = 400 To 1 Step -1
    If Cells(i, 7) = "Client" Then Cells(i, 1).EntireRow.Delete
Next i

Upvotes: 2

Related Questions