code stuck in endless loop

[manontheedge]

 
Sub Macro1()
 
 
Dim num As Integer
Dim scan As Integer
Dim start As Integer
Dim total As Integer
 
start = 1
 
While Range("A" & start) <> ""
    start = start + 1
Wend
 
total = start - 1
num = 1
 
For num = 1 To total
    For scan = (num + 1) To start
        While Range("A" & scan) = Range("A" & num)
            If Range("A" & scan) = Range("A" & num) Then
                Range("A" & scan).Delete shift:=xlUp
            End If
        Wend
    Next
Next
 
 
End Sub
 
 

this is VBA code for excel. It looks at column A, and counts up the number of non empty cells. Then, it looks to see if there are any repeats and deletes them out.

The problem I'm having is that it goes into an endless loop. I've stepped through the code, and when it reaches "End Sub", the program starts again from the beginning of the code. I have no idea why this is happening. Any help is appreciated.

[Killer42]

Hm... are you sure you haven't set up an event procedure to call this macro when a cell is changed, or something? If that were the case, then having your macro make a change to the sheet could cause it to be invoked again.

[manontheedge]

yeah, that code is literally all i have in the excel file ( i even opened a new file just in case and pasted the code ), except for some random data in column A. I went over it for quite a while and couldn't find anything to cause this loop.

i'm fairly new to VBA...is there any way to force the code to exit?

[Killer42]

yeah, that code is literally all i have in the excel file ( i even opened a new file just in case and pasted the code ), except for some random data in column A. I went over it for quite a while and couldn't find anything to cause this loop.

i'm fairly new to VBA...is there any way to force the code to exit?

I suppose you could try an Exit Sub or End statement. From the sound of it though, you are already exiting the sub normally at the end, and it is being re-invoked for some reason.

I suppose you might fall back on "standard" debugging techniques and try to narrow the focus. I'd suggest one of the following two methods:

1. Start commenting out parts of the code until it stops going wrong, or
2. Create a new macro, add in your code bit by bit and see at what point it starts going wrong.

You should be able to pin down what is causing the loop, even if that doesn't tell us why.

P.S. How are you invoking the macro? Just running it from the macros menu?

[manontheedge]

I tried commenting out one part at a time, and when I was trying to decide what to comment out I saw the problem. There is a while loop in the two FOR loops...the while loop continues while the previous cell is the same. The problem comes when they are both empty. The condition is always true. So I stuck in an IF statement before the while loop, and it works fine now. Thanks for the help man. Guess I just needed to break it down. Frustration got to me.

[Killer42]

I tried commenting out one part at a time, and when I was trying to decide what to comment out I saw the problem. There is a while loop in the two FOR loops...the while loop continues while the previous cell is the same. The problem comes when they are both empty. The condition is always true. So I stuck in an IF statement before the while loop, and it works fine now. Thanks for the help man. Guess I just needed to break it down. Frustration got to me.

Yeah, it can do that.

I find that when it comes to debugging, the important thing is breaking it down to the simplest level to identify the problem. Fixing it is generally the easy part.

Congratulations! :)


P.S. I still don't get why it would have been starting over at the beginning of the macro, though.