VBA Help - Inefficient Code

[vedderman]

I have this code I created that loops through lines of accounting data and moves offsetting lines to another sheet in the workbook called "Cleared." It works for the most part but after about 180 lines it starts to slow down and then almost comes to a complete stop.

I created an absolute value in column K and then sorted by that to get my debits/credits next to each other to start.

Sub DelZeros()
Do While ActiveCell.Value <> ""


    Do Until Abs(ActiveCell.Value) <> Abs(ActiveCell.Offset(1, 0).Value)
        If Abs(ActiveCell.Value) = Abs(ActiveCell.Offset(1, 0).Value) And _
        Abs(ActiveCell.Value) <> Abs(ActiveCell.Offset(-1, 0).Value) Then
        MyFirstRow = ActiveCell.Row
        End If


        MyValue = MyValue + ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    Loop
    MyLastRow = ActiveCell.Row
    MyValue = MyValue + ActiveCell.Value
    If MyValue >= -0.5 And MyValue <= 0.5 Then
        Range(Cells(MyFirstRow, ActiveCell.Column), Cells(MyLastRow, ActiveCell.Column)).Select
        Selection.EntireRow.Cut
        Sheets("Cleared").Select
        Range("A1048576").End(xlUp).Offset(1, 0).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Sheets("Q2").Select
        Selection.EntireRow.delete Shift:=xlUp
    Else
        ActiveCell.Offset(1, 0).Select
    End If
    MyValue = 0
Loop
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = False
 
Set WSCleared = Nothing
Set WSData = Nothing
 
MsgBox "Cleared " & LClearedCount & " line items"
 
End Sub

Wondering if someone can scan through this and tell me how to make it more efficient?

 

[Leith Ross]

Hello vedderman.

The big speed issues are Selecting, Cutting and Pasting the data, especially one row at a time. In VBA it is rare that you need to Select an object before you do something to it. You can speed up copying by formatting the destination area and then transferring the cell values all at once to the destination.

To help you amend the macro, I would need to see an small example of your data as it appears on the worksheet.