Why does this Worksheet_change loop crash if row insert causes number of rows > 100?

Question

The purpose of the below code is to number rows 6 to rowCount from 1 to rowCount in numerical order in col B. When the user inserts a row, the numbers automatically adjust. For example, if the user inserts a new row between rows 6 and 7, the new row is numbered 7 in col B, the previous row 7 is renumbered 8, and the remaining rows are renumbered 9 to rowCount. This works fine until rowCount >= 100. Then when the user inserts a new row, the program crashes. Why? What's so special about 100 and above? Is there a better method for auto re-renumbering the rows when the user inserts a new row?

Private Sub Worksheet_change(ByVal target As Range)

Dim i As Long, rowCount As Long

rowCount = UsedRange.Rows.Count

For i = 6 To rowCount
    If Me.Cells(i, 2) <> i - 5 Then
        Me.Cells(i, 2) = i - 5
    End If
Next

It does not necessarily crash. I just tested it (with more than 300 lines ...) and it works fine for me (Excel 2003 with compatibility pack on Windows XP). — Carsten Massmann, Aug 07 '13 at 21:47
Where did you insert the new row? Please try inserting a new row between 6 and 7. — jmaz, Aug 07 '13 at 21:50
You need to use `Application.EnableEvents = False`. If you search for that and Worksheet_Change, you'll get explanations of why. — Doug Glancy, Aug 07 '13 at 21:52
It does not seem to matter where I insert the lines: between 6 and 7, before line 6 or after line 7 ... Everything behaves as it should. Maybe you have another SUB somewhere hidden in your project that gets triggered by the change and the error is somewhere hidden inside that SUB? — Carsten Massmann, Aug 07 '13 at 21:53
@DougGlancy Is right: whenever the `If` condition is satisfied the `Worksheet_change` event fires again and again.. So the subroutine repeats itself. — Ioannis, Aug 07 '13 at 21:55
@DougGlancy and loannis, you are quite right. Added the Application.EnableEvents = False, which cleared it right up. Much obliged — jmaz, Aug 07 '13 at 21:58
Agree with @DougGlancy! See this http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640 — Siddharth Rout, Aug 08 '13 at 05:46
Good that the problems does not require fixing but it would be good to know what caused the problem. I see that the script would create many unnecessary loops but it does not appear to be an infinite loop. At worst, around 5,000 loops. Why would that cause a crash? — dra_red, Aug 08 '13 at 12:42
It would be infinite, actually as the `Change` event keeps creating a new one. Also, be aware that you shouldn't use SO's answer feature to ask questions. I'm going to flag this for closure. — Doug Glancy, Aug 08 '13 at 18:29
Doug Glancy provided the correct answer in his comments: Need to use Application.EnableEvents = False. You say there are unnecessary loops. Care to elaborate? — jmaz, Aug 08 '13 at 19:40

score 0 · Answer 1 · answered Sep 07 '13 at 02:43

Works fine here,

Option Explicit

Private Sub Worksheet_change(ByVal target As Range)
Application.EnableEvents = False
Dim i As Long
For i = 6 To UsedRange.Rows.Count
    If Me.Cells(i, 2) <> i - 5 Then Me.Cells(i, 2) = i - 5
    End If
Next
Application.EnableEvents = True
End Sub
'[![screenshot][1]][2]

Why does this Worksheet_change loop crash if row insert causes number of rows > 100?

1 Answers1