Excel 365 Macros running slow

[JackDanIce]

Hi,

Since yesterday, I've noticed a number of macros running slower than before. I was using Excel 2013 and about a month ago upgraded to Office.365

I have a range A1:AK39 where

A1 = Table name
A2:A39 Months of the year, starting Mar 2019, ending Mar 2022
A2:AK2 Months of the year, starting Mar 2019, ending Feb 2022
B3:AK39 values

The following code to normalise it into 3 columns took 15 seconds to run, which seems slow for the size of data (39 rows by 37 columns)

 Sub Generate_Normalise()    
    Dim r   As Range: Set r = Range("GRT_1")
    Dim x   As Long
    Dim y   As Long
    Dim i   As Long: i = 2
    Dim a   As Variant: a = r.Offset(1).Resize(r.Rows.Count - 1).Value
    Dim s   As Variant: s = Timer
    
    Application.ScreenUpdating = False


    With KPI_N
        .Cells.Value = ""
        .Cells(1, 1).Resize(, 3).Value = Array(r.Cells(1, 1).Value & "_Row_Month", r.Cells(1, 1).Value & "_Column_Month", "Value")
        For x = LBound(a, 1) + 1 To UBound(a, 1)
            For y = LBound(a, 2) + 1 To UBound(a, 2)
                If Len(a(x, y)) > 0 And x - 2 <> y Then
                    .Cells(i, 1).Resize(, 3).Value = Array(a(x, 1), a(1, y), a(x, y))
                    i = i + 1
                End If
            Next y
        Next x
    End With


    Application.ScreenUpdating = True
    Cells(1, 5).Value = Format((Timer - s) / 86400, "hh:mm:ss")
    
    Set r = Nothing: Erase a
    
End Sub

Part in green is only if a value exists in the range and part in blue is to ignore a formula cell.

A few ways to speed up, including pre sizing an output array, but, overall, I would expect above to run sub 5 seconds. I've had more complicated code in the past run faster than this.

Any similar experiences or suggestions?

TIA,
Jack

 

[Bagnon]

I've noticed that having the cursor hover over the Excel sheet makes macros run slower in Windows 10, but if you move to another window, it speeds it up.