While Loop Hanging up in VBA

Question

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!

Is the while loop necessary? It looks like your nested For loop would do the job by itself. — dosdel
– dosdel, Commented Nov 13, 2013 at 14:43
The while loop will never close as long as there is something in at least one cell in column J? Assuming you don't delete every single line, the loop won't close. — Joe Laviano
– Joe Laviano, Commented Nov 13, 2013 at 14:45
I would have thought the for loop would do it too, but if there are a couple in a row, excel deletes the first, and then the second one moves into the row number of the first, but the loop continues. I added the while to cover the range so the for loop would iterate through the range in J until it was empty, and for only being 28 cells to iterate through I don't see where it's getting held up. — Swolerosis
– Swolerosis, Commented Nov 13, 2013 at 14:59

dosdel · Accepted Answer · 2013-11-13 15:01:38Z

1

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

answered Nov 13, 2013 at 15:01

dosdel

1,1389 silver badges11 bronze badges

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

2 Comments

Swolerosis Over a year ago

I get what you are trying to do with the Step -1, but for some reason that doesn't work. I can however add {InvList = InvList - 1} to the If statement and that seems to work.

dosdel Over a year ago

really? Strange, I tested the code and it works for me. Your solution should work fine too though.

Collectives™ on Stack Overflow

While Loop Hanging up in VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related