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, Aug 22 '16 at 01: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 秀, Aug 22 '16 at 01: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, Aug 22 '16 at 01:32
@Ellen Try to run inner loop first. Make the outer loop as a comment to see if the inner loop works properly or not. Try also my advice in my first comment — Anastasiya-Romanova 秀, Aug 22 '16 at 01:42
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, Aug 22 '16 at 02: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, Aug 22 '16 at 02:21
It also seems rather strange to be deleting certain columns and shifting cells **up** while doing so, then proceeding to the next group of 4 columns and repeating the process. — YowE3K, Aug 22 '16 at 02:49

atclaus · Accepted Answer · 2016-08-22T03:22:01.020

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 '16 at 03:22

answered Aug 22 '16 at 01:43

atclaus

1,046
1
9
12

Corrected my answer – atclaus Aug 22 '16 at 01:53
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. – Tee Aug 22 '16 at 13:19
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! – atclaus Aug 22 '16 at 20:31
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. – Tee Aug 23 '16 at 23:11

VBA excel delete endless loop

1 Answers1