Excel 2016 VBA - Copy is extremely slow

user_9459812 0
Viewed 109

I recently upgraded excel from 2010 to 2016(32bits) and suddenly the code that took 5sec to complete now takes forever. Even simple copy operation like the code below takes about half a minute to run.

For i = 1 To 10
    Worksheets("Sheet2").Range("A" & i).Copy Worksheets("Sheet2").Range("A" & i)
Next i

Is there anyway to fix this?

EDIT: Below is the "real" code that @Comintern requested, there are about 1000 copies but it takes forever to run:

For i = 3 To lRow1        
     Select Case Range(cType & i).Value
        Case "Order"
            ws1.Rows(i).Copy wsOrder.Rows(lRowOrder + 1)
            lRowOrder = lRowOrder + 1
        Case "UP"
            ws1.Rows(i).Copy wsUP.Rows(lRowUP + 1)
            lRowUP = lRowUP + 1
        Case "Cancel"
            ws1.Rows(i).Copy wsCancel.Rows(lRowCancel + 1)
            lRowCancel = lRowCancel + 1
        Case "Edit"
            ws1.Rows(i).Copy wsEdit.Rows(lRowEdit + 1)
            lRowEdit = lRowEdit + 1
        Case ""
            ws1.Rows(i).Copy wsOther.Rows(lRowOther + 1)
            lRowOther = lRowOther + 1
    End Select

Next
ms-office vba excel
1 Answers

I recently had a similar problem. Excel would take several minutes to copy a part of the worksheet, say an area of 12x500 cells. In my case, it turned out that the cells had picked up some invisible graphics, probably from their origin on a webpage. So, one thing to check for, if you experience excel hanging up on copy operations, is the presence of image objects in your work.

There's a quick procedure here to find and remove them: https://excel.tips.net/T003018_Deleting_All_Graphics.html

user_20138940
Related