VBA embedded image glitch - One sheet fast the other slow

[VBAmonkey]

Long time reader first time poster. I've been using VBA for 6+ years now. I have a problem with a process that is largely exposed with a certain section of code. I have attached a link to the file for reference.

The file:- Contains 100 embedded images and minimal text
The code:- loops through and reformats 100 ranges of cells

On my old laptop(I5,8Gb Ram,Win7 and Office 2013) - This macro runs in 0.68 seconds on average
On my new XPS(I7,16Gb Ram, Win10 and Office 2013) - This macro runs in 20 seconds on average
I have tested on a few different computers running different windows/office and the results are between the two above.

Now here is the interesting part. I created a new file, copied over the images and formatted everything the same and then it ran at 0.5seconds(on the XPS). When I ran it a second time it was then up at 20 seconds. I duplicated the sheet in the same file and one sheet runs at 0.5 seconds and the other runs at 20 seconds. If I delete the images in the slow sheet then the speed is back down at 0.5 seconds also so the images are the culprit but I need them for the way this sheet(and others) are formatted rather than using conventional headers.

This code is part of a larger section of code in a very large and highly used spreadsheet and so this 20 seconds is amplified significantly in reality.

Can anyone replicate these results with the linked sheet on their computer, and if so can anyone tell me what is different between the sheets so that I can finally solve the problem?

Book1.xlsm

drive.google.com

 

[yky]

Sheet1: first run 1.1 seconds, subsequent runs around 1.1 seconds,
Sheet2: first run 4.6 seconds, subsequent runs around 1.1 seconds.

 

[VBAmonkey]

Bump

 

[RoryA]

Sheet 1 runs between 0.5-0.7 seconds for me. Sheet2 takes just over 1s the first time and between 0.5 and 0.7 thereafter. I do note that the used range on sheet 2 is about double that of sheet 1.

 

[VBAmonkey]

Thanks, I'll look into the used range as it is different even though the No. of filled cells are the same on each sheet.

I tried it on 8 computers before creating the thread. It ran slow(10-20 seconds) on 5 of them and the fast sheet was always fast.

I think it might be a glitch in the computer settings or excel itself. For some reason, something changes in the sheet properties and any objects on the sheet slows the sheet processing down and make the sheet laggy, glitchy and ultimately slow to move around and edit when there are lots or dense chart.

 

[VBAmonkey]

After investigating further with the lead from RoryA, I found that these was corrupt formatting outside the used range that was slowing the sheet down. I am still unable to explain why it only slows down some systems but was able to find out that clearing all the cells outside of the used range repairs the sheet. I used the code below if anyone has the same issue.

Also, if anyone is able to explain why this happens and why it may only slow some computers I would still greatly appreciate the advice.

Sub clearFormatOutsideUsedRange()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set sht = ActiveSheet
sht.UsedRange 'Refresh UsedRange
lastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
LastCol = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
lLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
RowEnd = Cells(lastRow, 1).End(xlDown).Row
ColEnd = Cells(LastCol, 1).End(xlToRight).Column
Range(Cells(lastRow, 1), Cells(RowEnd, ColEnd)).Clear
Range(Cells(1, LastCol), Cells(RowEnd, ColEnd)).Clear

Application.ScreenUpdating = True
Application.Calculation = calcState
End Sub