CODEWITHSUNDEEP
vbaexcel
dom176
dom176

Reputation: 187

Code Runs well on debug but crashes during full execution

I have a line of code that runs flawlessly on debug, but consistently crashes excel on full execution. Essentially what it is supposed to do is combine two rows of data. I am doing this because I imported data from a database, in that database it records transactions on two separate lines: one side of the transaction is on one line while the other side is on the other line, all of data for the previous columns is located on either one of these lines. So for example:

|trans 1 | 1/1/2015| $500.00| 0

|trans 1 |---------| 0| 497.00|

Ideally it should look like

|trans 1|1/1/2015|500.00|497.00|

Second row should be deleted.

This is my code to do this:

Sub Collapse_Rows()
    Dim ALLCS As Worksheet

    Set ALLCS = Sheets("Asset LLC (Input)")
    On Error Resume Next
    Application.EnableEvents = False

    For x = 66515 To 16 Step -1
        If ALLCS.Cells(x, 2) = ALLCS.Cells(x + 1, 2) Then
            If ALLCS.Cells(x, 28) <> Empty Then
                ALLCS.Cells(x + 1, 28) = ALLCS.Cells(x, 28)
            End If
        End If

        If ALLCS.Cells(x - 1, 2) = ALLCS.Cells(x, 2) Then
            If ALLCS.Cells(x, 28) <> Empty Then
                ALLCS.Cells(x - 1, 28) = ALLCS.Cells(x, 28)
            End If
        End If

        If ALLCS.Cells(x, 5) <> Empty And ALLCS.Cells(x, 2) = Empty Then
        ElseIf ALLCS.Cells(x, 24) = Empty Then
            ALLCS.Cells(x, 24).EntireRow.Select
            Selection.Delete
        End If
    Next
End Sub

I run this code partially on debug and it works well. When I run the code fully excel crashes any ideas as to why/how to prevent this from happening??

Upvotes: 0

Views: 802

Answers (1)

basodre
basodre

Reputation: 5770

At first glance, it seems like a major part of the problem lies in the fact that the code itself is somewhat resource instensive.

First, it starts at row 66515. Is there a reason it starts here? That means that in most circumstances, there will be around 33,250 row deletion executions (every other row from 16 --> 66515). That will take a long time, and in many cases Excel will become unresponsive.

A few options.

Determine whether or not you actually need to start the loop at row 66515. Is that a fixed starting point for a reason? If not, use code to determine the last row of your actual dataset and start there.

Also, at the beginning of your code, turn SceenUpdating off using: Application.ScreenUpdating = False. At the end of your code, turn it back on using Application.ScreenUpdating = True

Upvotes: 2

Related Questions