CODEWITHSUNDEEP
excelvba
Aaron Parisi
Aaron Parisi

Reputation: 706

Why is .autoFill causing Excel to crash?

I have a spreadsheet, here's a snippet of it:

enter image description here

Ideally, when I add a date to colC, various columns would autofill the dates down to the last row with a closing date. Here's my code (which has some sorting stuff in it also, which is working fine); the part after I define lastDrag is where the issue is, I think:

Private Sub Worksheet_Change(ByVal Target As Range)
'On Error Resume Next

Dim firstRow As Long
Dim insRow As Long
Dim lastRow As Long

If Not Intersect(Target, Range("A:AC")) Is Nothing Then

    With ActiveWorkbook.ActiveSheet

        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        .Sort.SortFields.Clear

        .Sort.SortFields.Add(Range("AC1:AC" & lastRow), _
            xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(191, 191, 191)

        ' ^^ sorts the "gray" (closed) exchanges at the bottom)

        .Sort.SortFields.Add Key:=.Range("AC1:AC" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

        ' ^^ sorts closed files by file close date

        .Sort.SortFields.Add Key:=.Range("C1:C" & lastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

        ' ^^ sorts open files by RQ close date
        ' THIS IS WHERE CONDITIONS SHOULD BE
        ' IF no id has been entered, sort by...
        ' IF id has been entered, sort by...

        .Sort.SortFields.Add(Range("K1:K" & lastRow), _
            SortOn:=xlSortOnCellColor, Order:=xlDescending, DataOption:=xlSortNormal).SortOnValue.Color = xlNone

        ' ^^ makes sure that the non-colored rows are sorted??

        With .Sort
            .SetRange Range("A1:AC" & lastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        lastDrag = .Cells(.Rows.Count, "C").End(xlUp).Row

        Range("D2").Select
        Selection.AutoFill Destination:=Range("D2:D" & lastDrag), Type:=xlFillDefault
'        ^^ this seems to work but it loops forever...


    End With

End If

End Sub

Currently, it seems like Excel is autoFill-ing over and over until it crashes. Why?

Is there a way to get it to autofill columns D, E, H, J, etc., (i.e. a bunch of non-adjacent columns) all in one shot? I had some stuff like Range("D2,E2,H2..." & lastDrag)...

Upvotes: 1

Views: 655

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Private Sub Worksheet_Change(ByVal Target As Range)

This worksheet event is fired every time the worksheet changes... whether that's triggered by the user... or by your own code. You need to set EnableEvents to False to prevent re-entry, and back to True once you're done - whether an error is raised or not:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    Application.EnableEvents = False

    '...code...

CleanExit:
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    Stop 'debug me
    Resume CleanExit
End Sub

When you call a procedure from another, the new procedure is pushed onto the call stack; when that procedure exits, it gets popped/removed from the call stack. Normally that isn't a problem, but if things get recursive and out of control, eventually the call stack can't get any deeper, and the VBA runtime blows up.. in this case taking down the host application (Excel) with it.

Upvotes: 2

Michal
Michal

Reputation: 5882

Think about what your code is doing - once a change is detected in columns A:AC your code is triggered to do something. And when it does something, it does it in columns A:AC creating a continuous loop which is going to eventually crash. Once you have detected a worksheet event and before you have start data manipulation you have to tell Excel to stop detecting new events until the code is completed.

You need to add Application.EnableEvents = False immediately after the IF statement and turn it back on again Application.EnableEvents = True just before you exit the sub.

Upvotes: 1

Related Questions