Loop runs, but gets "not responding" for a few seconds?

Question

I'm creating yearly budgets. When a user clicks "Create New Budget" this loop runs to determine the most recent budget year for that area.

The loop executes but receives a "Not responding" error for a few seconds before it finishes.

For i = 2 To Rows.Count
    cellYearRaw = Sheets("Budgets").Cells(i, "A").Value
    cellYear = CInt(cellYearRaw)
    cellAreaKey = Sheets("Budgets").Cells(i, "H").Value
    If cellYear > year And cellAreaKey = areaKey Then
        year = cellYear
    End If
Next i

It would be best to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) instead of looping all the way to the very bottom of the worksheet. — BigBen, Dec 09 '20 at 14:47
That message occurs when there hasn't been any activity on the screen for awhile. The Office app gets nervous ... what I've done in long running routines is put put a small form and put into it a running counter. This way the user knows something is happening and the Office app stays silent because action is happening on the screen. — Rich Michaels, Dec 09 '20 at 14:57

score 0 · Answer 1 · answered Dec 09 '20 at 16:39

Your code needs to force the events to excel tells to system that it is responding. You need to use DoEvents:

For i = 2 To Rows.Count
    cellYearRaw = Sheets("Budgets").Cells(i, "A").Value
    cellYear = CInt(cellYearRaw)
    cellAreaKey = Sheets("Budgets").Cells(i, "H").Value
    If cellYear > year And cellAreaKey = areaKey Then
        year = cellYear
    End If
    DoEvents 'for each line can be slow. Use for exemple "If i Mod 10 = 0 Then: DoEvents", to activate events after 10 rows.
Next i

Loop runs, but gets "not responding" for a few seconds?

1 Answers1