VBA excel delete endless loop

Question

I have used logic from other stack exchange problems with a similar issue but I CANNOT figure it out. (good examples looked at here: VBa conditional delete loop not working) I'm sure it is really simple. Why is my code getting stuck in an endless loop here?

I'm just replacing a value and then deleting a range of rows. Any ideas what's wrong? Double loop issue possibly? I've looked at it too long and it all seems logical to me. Any help would be much appreciated.

i = 4
Do While i < 10
    j = 0
    Do While j < 24
        ho = Cells(i, 69 + j)
        If 0 < ho < 3 Then
            k = Cells(i, 67 + j)
            Cells(i - 1, 67 + j) = k
            Range(Cells(i, 66 + j), Cells(i, 69 + j)).Delete (xlShiftUp)
        Else
            j = j + 4
        End If
    Loop
    i = i + 1
Loop

Don't stick the statement incrementing your loop-counter inside an if-statement. Unless your code is always executing the else-part of your statement, j will be stuck at a static value and never reach 24. — eirikdaude
– eirikdaude, Commented Aug 22, 2016 at 1:27
To make it more clearly, put DoEvents and Debug.Print j between the statement End If and Loop. Observe the value j in the Immediate Window — Anastasiya-Romanova 秀
– Anastasiya-Romanova 秀, Commented Aug 22, 2016 at 1:30
That's a good idea, but I have it like that because I want to keep checking my rows (since they are being deleted) to make sure ho hasn't changed. So only increment if nothing gets deleted. Right? Maybe I am wrong here? — Tee
– Tee, Commented Aug 22, 2016 at 1:32
when u delete rows, its better to do the reverse iteration that is start with e.g. i = 10,000 then iterate i = i - 1. this is because when u delete rows, the row number changes. otherwise u have to consider the change in row number for the bottom data in ur row deletion. — Rosetta
– Rosetta, Commented Aug 22, 2016 at 2:05
Your statement If 0 < ho < 3 Then is interpreted as either (I can't remember which it will be) If (0 < ho) < 3 Then or If 0 < (ho < 3) Then. In either case, it is comparing True/False with a number. You probably want to change that statement to If 0 < ho And ho < 3 Then. (I don't know whether this is a cause of your problem, but it will certainly mean it isn't doing what you expect.) — YowE3K
– YowE3K, Commented Aug 22, 2016 at 2:21

atclaus · Accepted Answer · 2016-08-22 03:22:01Z

0

Change your if statement. This will stop it when there is no more data.

If 0 < ho AND ho < 3 AND ho<>"" Then

edited Aug 22, 2016 at 3:22

answered Aug 22, 2016 at 1:43

atclaus

1,1861 gold badge9 silver badges14 bronze badges

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

4 Comments

atclaus Over a year ago

Corrected my answer

Tee Over a year ago

Yes! This stops the endless looping. Thank you, I never thought to mess with my if statement! However, it is not eliminating any cells... for instance 0.3 (ho is a double) and every other value that should disappear does not change. I think this may be a loop issue. I will try Anastasiya's advice tonight because I am not sure what's going on. I know it is strange to proceed to next 4 columns but that is simply how the data is structured.

atclaus Over a year ago

If it answered your question, can you please mark it as the answer? I am not following the rest of your question (if it is one) but maybe another full question and some of us will help! Good luck!

Tee Over a year ago

i feel strange checking it as my answer because my macro doesn't do anything or work correctly but yes, it does fix my endless loop.

Collectives™ on Stack Overflow

VBA excel delete endless loop

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related