This is so slow I have to run it overnight

TheWennerWoman

Active Member
Joined
Aug 1, 2019
Messages
301
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have inherited a file that needs to be run overnight as it ties up Excel and takes hours. The relevant piece of code is below, is there anything obvious that leaps out at you gurus as being bad practice and that can be replaced.......anything that gives a speed improvement would be really appreciated.

VBA Code:
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

Thanks in advance.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
The whole thing looks painful to me. Lots of looping and copying/cutting single rows at a time. Plus updating the status bar adds to the runtime. Hard to tell in isolation why it's written how it is, or whether there is any optimization in place at all, such as turning off screenupdating and events.
 
Upvote 1
The whole thing looks painful to me. Lots of looping and copying/cutting single rows at a time. Plus updating the status bar adds to the runtime. Hard to tell in isolation why it's written how it is, or whether there is any optimization in place at all, such as turning off screenupdating and events.
Anyone else? Otherwise I'll concede defeat. :-)
 
Upvote 0
How many rows are you looking at processing ?
Try using
VBA Code:
Application.screenupdating = false
at the start of the code and
VBA Code:
Application.screenupdating =True
at the end of the code
Get rid of the status bar as a test for time usage.
Also look at using Autofilter to copy data in a block rather than line by line.
And this section, probably going to be a killer.
VBA Code:
 If z = 1 Then
        For i = 3 To lrow1
By cutting rows from the top down rather than the bottom up, means every time you cut a row, the entire worksheet has to move up 1 row, no big deal if there are only a few rows, but a real problem if there are thousands.
Also look at where lrow takes you to....if it's waaay down the worksheet, that could also extend the process greatly
 
Upvote 1
It would help to see the full code, as well as a copy of your data using the XL2BB add in, or share a desensitised version of your file via Google Drive, Dropbox or similar file sharing platform.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top