Are these bad coding? What is slowing these down?

tom88Excel

New Member
Joined
Sep 29, 2014
Messages
30
Can someone please help me? After I upgrade Office from 2010 to Office 365, all the codes become very slow. I'm looking into the causes. I came across this really simple codes and pulled out a section. I wanted to ask all of you experts why these simple codes would take such a long time to run? This took 7 seconds to run under my Office 365 when it should have been under a second.

VBA Code:
Application.CutCopyMode = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False

Dim StartTime As Date
Dim rw As Integer, NewRow As Integer, RowstoAdd As Integer, j As Integer
StartTime = Now()

    rw = 11
    LastRow = 144
    RowstoAdd = 2
    TheEndRow = LastRow
    
Do While rw <= TheEndRow
    If Cells(rw, 14) <> Cells(rw + 1, 14) Then
        For j = 1 To RowstoAdd
            Rows(rw + 1).EntireRow.Insert
        Next j
        rw = rw + RowstoAdd + 1
        NewRow = NewRow + RowstoAdd
        TheEndRow = NewRow + LastRow
    Else
        rw = rw + 1
    End If
Loop
MsgBox Round((Now() - StartTime) * 24 * 60 * 60, 0) & " Seconds"

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.CutCopyMode = True
Application.DisplayAlerts = True

As you can see I'm trying to add 2 rows when the values on column N are different. On this spreadsheet, I have formula from Rows 11 to 144, and from Columns A to K and from N to P, so this worksheet is not that large.

I notice that if I copy all the formulas and paste it as value, it took 6 seconds to complete, a second faster even I have the manual calculation turned off?

In further testing,
If I copy that page (With formulas) to a brand new workbook (Book 1.xlsx), it took 3 seconds to run.
But If I just copy the whole page from the original workbook and paste as the value on a brand new workbook (Book 2.xlsx), it only took 0.1 seconds to run.

Here is the result I got under different scenarios:
Run Time
( in seconds)
Set things to false at the beginning of the codeWorkbookFile SizeComment
13NOrig.xlsm1.8 MBDid not turn anything to false in the beginning
12Everything off, except for calculationOrig.xlsm1.8 MBSet everything to False, except for calculation in the beginning
7Turn off Calculation onlyOrig.xlsm1.8 MBTurn off calculation only
7YOrig.xlsm1.8 MBSet everything to False in the beginning
6YOrig.xlsm1.8 MBCopy all formula and paste as value
3YBook 1.xlsx308KCopy that 1 worksheet and save as Book 1
3YBook 1.xlsx308KCopy all formula and paste as value
0.1YBook 2.xlsx20.3KCopy Only Value and Format from that 1 page on Orig.xlsm and paste it to a new workbook name "Book 2"

My point is, what is dragging up the time other than Auto Calculation? Switching off ScreenUpdates, Status Bar, etc. only saved me 1 second. What else is taking the time to run? Do file sizes or the number of tabs in a workbook matter? I thought setting everything to false at the beginning would stop everything unnecessary from running? What else can I turn off?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,936
Office Version
  1. 365
Platform
  1. Windows
Have you tried going from the bottom up?

VBA Code:
    rw = 144
    RowstoAdd = 2

    Do
        If Cells(rw, 14) <> Cells(rw - 1, 14) And Cells(rw, 14) <> "" Then
            Rows(rw).Resize(RowstoAdd).Insert
        End If
        
        rw = rw - 1
        
    Loop Until rw <= 11
 

tom88Excel

New Member
Joined
Sep 29, 2014
Messages
30
You're a genius Norie. Your Syntax is so much shorter, and it only took 3 seconds to run, cut down more than half of the time. Thank you so much!!!
But I still wonder... I there anything else I can do to make it even faster, for example, downsize my workbook, remove shapes, etc.. I wonder if those things would slow things down....
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,936
Office Version
  1. 365
Platform
  1. Windows
I can't really think of anything else that could improve the speed.

I suppose you could possibly do something with arrays, e.g. read the existing data into an array, create a new array with the blank rows and then output that.

That would only really work if you were only dealing with values.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,479
Messages
5,548,276
Members
410,825
Latest member
Dave12
Top