Solved: Code randomly stops without CancelKey Disabled

[JimS]

In order to get the following code to run cleanly I must add “Application.EnableCancelKey = xlDisabled” before it.

Without the CancelKey Disabled it will randomly stop.

Any obvious errors?

Thanks...

JimS



Sub count()
Application.ScreenUpdating = False

lengthB = 0

lengthB = Worksheets("Sheet1").Cells(Rows.count, 2).End(xlUp).Row
minVal = Range("Low")

dropCounter = 0

cellCounter = 0

For i = 1 To (lengthB - 2)
tot1 = WorksheetFunction.Round(Sheets("Sheet1").Range("B" & i).Value, 5)
If tot1 < minVal Then
tot2 = WorksheetFunction.Round(Sheets("Sheet1").Range("B" & i + 1).Value, 5)
tot3 = WorksheetFunction.Round(Sheets("Sheet1").Range("B" & i + 2).Value, 5)
tot4 = WorksheetFunction.Round(Sheets("Sheet1").Range("B" & i + 3).Value, 5)

If tot1 >= tot2 And tot2 >= tot3 And tot3 >= tot4 Then
MsgBox i
dropCounter = dropCounter + 1
Do While minVal >= tot1
Sheets("Sheet1").Range("B" & i).Select
Selection.Delete Shift:=xlUp
lengthB = lengthB - 1
cellCounter = cellCounter + 1
If IsEmpty(Sheets("Sheet1").Range("B" & i).Value) Then
Exit For
Else
tot1 = WorksheetFunction.Round(Sheets("Sheet1").Range("B" & i).Value, 5)
End If
Loop
End If
End If
Next i
Sheets("# Removed").Range("K2").Value = dropCounter
Sheets("# Removed").Range("M2").Value = cellCounter


Application.ScreenUpdating = True

End Sub

[p45cal]

Without having scrutinsed you code very closely, the only thing that would worry me (and this may not be related) is that you have a loop variable (i) counting UP, which runs DOWN the range:
For i = 1 To (lengthB - 2)

and in that loop you have (effectively):

Sheets("Sheet1").Range("B" & i).Delete Shift:=xlUp

which means you don't examine all the rows (if anything gets deleted);
Let's say i was 10 and it needs deleting. Let's also say that something in the row below (row 11) will also need deleting. So while i is 10 you delete something, and immediately you do that, the stuff that was on row 11 below, moves up to row 10, but before it gets examined, the Next i instruction gets executed, incrementing i to 11, so the subsequent row gets examined. What was in row 11 before stuff above it got deleted never gets looked at. Solution: loop upwards through the range rather than downwards:
For i = (lengthB - 2) to 1 step -1

Now all the rows get looked at.

[JimS]

Thanks...

[p45cal]

Thanks...

but did it solve the code randomly stopping?

[mdmackillop]

I periodically have this problem and never found a full solution. CodeCleaner (http://www.appspro.com/Utilities/CodeCleaner.htm) can help.

[Aflatoon]

Ditto, except for the Code Cleaner bit (makes no difference). As best I can tell, it seems to relate to MS updates being applied. (Fortunately, I have not suffered it for a couple of months now)