VBA - deleting rows with conditions in loop [duplicate]

Question

I have to delete all rows that have to cells equal in VBA excel. Here is what i wrote:

Dim i As Long, j As Long
j = Rows.Count
For i = 1 To j
    If Cells(i, 17).Value = Cells(i, 19).Value Then Rows(i).Delete
Next i

But this code is not deleting every line I am looking for. If there are 2 rows with matches the conditions respectively, Excel jumps to another one and leaves this (which is logical), I do not know how to adapt this loop to coming back and delete every searched line

always loop backwards when deleting rows For i = j To 1 Step -1 — Shai Rado
– Shai Rado, Commented Dec 2, 2016 at 14:51
You should check out my answer to Compare cells to delete rows, value is true but not deleting rows. It demonstrates the effects of deleting while iterating over a loop. — user6432984
– user6432984, Commented Dec 2, 2016 at 15:47

David Zemens · Accepted Answer · 2016-12-02 15:14:30Z

2

What I am understanding from your code is , it is not iterating all the lines since one of the conditions are getting fulfilled . My recommendation is to reverse loop it. Iterate from the last by using "-1"

Instead of:

For i = 1 To j
    If Cells(i, 17).Value = Cells(i, 19).Value Then Rows(i).Delete
Next i

Do this:

For i = j to 1 Step - 1
    If Cells(i, 17).Value = Cells(i, 19).Value Then Rows(i).Delete
Next i

edited Dec 2, 2016 at 15:14

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

answered Dec 2, 2016 at 14:56

Ranadip Dutta

9,5493 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

David Zemens Over a year ago

This would be a helpful answer if you demonstrated example code.

Ranadip Dutta Over a year ago

"For i = j To 1 Step -1" Just put it in you code instead of the existing For loop. Sorry I am not able to test it at this point since my msoffice is having some issue.

Cezary Over a year ago

Thanks, it helped a lot!

Ranadip Dutta Over a year ago

If it really helps you then please like the answer cause that will help others also. :)

David Zemens Over a year ago

@RanadipDutta it's preferable to put a working example of the code in the answer itself, not in the comments. You can edit your answer to be more valuable to others.

|

Thomas · Accepted Answer · 2016-12-02 14:58:52Z

0

When you delete a row you have to decrement the value of i and update the value of j. Because when you delete a row:

the following row is now at the i position
the number of rows (j) is reduced by 1 row

answered Dec 2, 2016 at 14:58

Thomas

3832 silver badges9 bronze badges

1 Comment

David Zemens Over a year ago

Good explanation. This is one possible solution, but the generally preferred approach is to do backwards iteration (e.g., For i = 100 to 1 Step - 1), rather than decrementing a loop counter variable.

Collectives™ on Stack Overflow

VBA - deleting rows with conditions in loop [duplicate]

2 Answers 2

7 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Linked

Related