CODEWITHSUNDEEP
vbaexcelwhile-loop
Swolerosis
Swolerosis

Reputation: 93

While Loop Hanging up in VBA

I have a short blip of code that labels duplicates and deletes the lines out of a worksheet.

The part that identifies the duplicates just adds the word "Duplicate" to the right of the data in Column J, and that part seems to be working okay.

THe other part looks like it works fine too, but once it's done it just hangs up excel and i have to force it closed. Here is the code for the delete part:

While Not IsEmpty(Range("J2:J" & InvLast))
        For InvList = 2 To InvLast
            If Range("J" & InvList).Value = "Duplicate" Then
                Range("J" & InvList).EntireRow.Delete
            End If
        Next InvList
Wend

InvLast in the test file i'm working on is only 28 lines, but I'll need to be capible of a couple thousand lines without taking a day to run.

Thanks in advance!

Upvotes: 1

Views: 946

Answers (1)

dosdel
dosdel

Reputation: 1138

Get rid of the While loop, and use this method so that you won't skip rows:

Sub deleteDuplicates()
    For InvList = InvLast To 2 Step -1
            If Range("J" & InvList).Value = "Duplicate" Then
               Range("J" & InvList).EntireRow.Delete
            End If
    Next InvList
End Sub

Upvotes: 1

Related Questions