Slow VBA routine; code appraisal?

[TheWennerWoman]

Hello,

Hope someone can help. I have inherited a routine that takes around three hours to parse approximately 60,000 rows of data.

What it has to do is cut data from an initial dataset (the 60,000 rows) and then paste into either of two other sheets depending on some criteria. I have produced the code below; I appreciate that it's probably not viable for an in-depth look at how it's doing its thing without the underlying dataset but, just as an overview, does anything in the code jump at your expert eyes as a "why do it that way?". Or does the code look about as efficient as it's going to get?

Thank you for your time; the code is:

lrow = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row
x = Sheet1.Range("N" & lrow).Value

For y = x To 1 Step -1

lrow = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row

    Do Until y <> Sheet1.Range("N" & lrow).Value
    Sheet1.Rows(lrow).Copy
    Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheet1.Rows(lrow).EntireRow.Delete
    lrow = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row
    Loop

    lrow1 = Sheet2.Cells(Sheet2.Rows.Count, "B").End(xlUp).Row
    Sheet2.Range("O1:T1").Copy Sheet2.Range("O3:T" & lrow1)
    Sheet2.Range("O3:T" & lrow1).Copy
    Sheet2.Range("O3:T" & lrow1).PasteSpecial xlPasteValues
    z = Application.WorksheetFunction.Sum(Sheet2.Range("Q:Q"))
    If z = 1 Then
        For i = 3 To lrow1
        If Sheet2.Range("Q" & i).Value = 0 Then
        Sheet2.Rows(i).Cut Sheet3.Cells(Sheet3.Rows.Count, "A").End(xlUp).Offset(1, 0)
        Else
        Sheet2.Rows(i).Cut Sheet5.Cells(Sheet5.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
        Next i
    Else
    Sheet2.Rows("3:" & lrow1).Cut Sheet4.Cells(Sheet4.Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
        
        
Application.StatusBar = "Progress: " & y & " remaining. " & Format(y / x, "0%")

Next y

 

[Peter_SSs]

When you copy or cut and paste, you reproduce formulas, formatting etc. If you are not interested in that but just in the values being transferred to other places then a much faster way would be to load the data into an array in memory and deal with it there then write the results back to the sheet(s) at the end.

To suggest specific code, it would be helpful to see a small set of dummy sample data, the results and an explanation in relation to that data. My signature block below has help with how to post sample data