Are these bad coding? What is slowing these down?

[tom88Excel]

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.

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 code	Workbook	File Size	Comment
13	N	Orig.xlsm	1.8 MB	Did not turn anything to false in the beginning
12	Everything off, except for calculation	Orig.xlsm	1.8 MB	Set everything to False, except for calculation in the beginning
7	Turn off Calculation only	Orig.xlsm	1.8 MB	Turn off calculation only
7	Y	Orig.xlsm	1.8 MB	Set everything to False in the beginning
6	Y	Orig.xlsm	1.8 MB	Copy all formula and paste as value
3	Y	Book 1.xlsx	308K	Copy that 1 worksheet and save as Book 1
3	Y	Book 1.xlsx	308K	Copy all formula and paste as value
0.1	Y	Book 2.xlsx	20.3K	Copy 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?

 

[Norie]

Have you tried going from the bottom up?

    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]

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]

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.

 

[tom88Excel]

Thank you for your input Norie, that is really helpful.