CODEWITHSUNDEEP
excelvba
Deke
Deke

Reputation: 495

Deleting Entire rows with if when there are more than 2 duplicates

BeforeAfter

It's probably something simple I've over looked but I think I need some fresh eyes on it. I've setup a formula that sets duplicates as either "K" to keep or "D" to delete. It works fine if there are only 2 duplicates but if there are 3 or more it will delete only 1 duplicate leaving the rest. Any help would be greatly Appreciated!

Set wsl = ActiveWorkbook.Worksheets(1)
lr = wsl.Range("A" & wsl.Rows.Count).End(xlUp).Row

For d = 2 To lr
    If wsl.Range("J" & d).Value Like "*D*" Then
       wsl.Range("J" & d).EntireRow.Delete
Else
End If
Next d

Upvotes: 1

Views: 82

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

When deleting rows, you should start at the end, and work toward the top. You should just need to change your For loop beginning to

For d = lr to 2 Step -1

If you use F8 to step through the code as you have it, you'll notice that when you delete a row, that row under it becomes the "current row". Your code then immediately moves on to the next row, without checking the one you brought up a row.

Working from the end, to the start, avoids this issue.

Upvotes: 1

Related Questions