VBA Script takes 3 Minutes to run - Any way to make it faster?

[Nordicrx8]

Hi all!

The below code works perfectly, but it takes a very long time for it to do what it needs to do. Is there anything I can do to make it run faster? It's not a massive amount of data, but I am transposing it. I just didn't expect it to take this long to perform, and at it's current state, isn't a viable option.

Any thoughts or recommendations appreciated!

Private Sub Update_Master()

    With Sheets("Main")

        Dim FirstEmptyRow As Long
        FirstEmptyRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

        Sheets("Sch_Pricing").Range("B2:M16").Copy
        .Cells(FirstEmptyRow, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Sheets("Sch_Pricing").Range("B17:M37").Copy
        .Cells(FirstEmptyRow, "FJ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        
        Sheets("Material").Range("B5:M152").Copy
        .Cells(FirstEmptyRow, "R").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Excel.Application.CutCopyMode = False
    End With
End Sub

 

[Joe4]

It shouldn't take that long to do that.
Do you have any event procedure code that is being automatically triggered that could be interfering?

See if these updates help:

Private Sub Update_Master()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With Sheets("Main")

        Dim FirstEmptyRow As Long
        FirstEmptyRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

        Sheets("Sch_Pricing").Range("B2:M16").Copy
        .Cells(FirstEmptyRow, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Sheets("Sch_Pricing").Range("B17:M37").Copy
        .Cells(FirstEmptyRow, "FJ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        
        Sheets("Material").Range("B5:M152").Copy
        .Cells(FirstEmptyRow, "R").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Excel.Application.CutCopyMode = False
    End With
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub

 

[gitmeto]

Lately I have been finding some benefit using Power Query for such tasks. You would need to select the range that you want and convert it to a table, you can then insert the table on your "Sch_Pricing" sheet after you go through the wizard that is located on the "Data" tab next to where it says "Get Data". There you will see the option "From Table/Range".

You can then set the query to run when the table changes via a macro. I am not sure if this would speed things up, but it reduces the amount of code that you need to write in a macro and takes advantage of the built in features within Excel to accomplish your goal.

 

[Nordicrx8]

It shouldn't take that long to do that.
Do you have any event procedure code that is being automatically triggered that could be interfering?

See if these updates help:

Private Sub Update_Master()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With Sheets("Main")

        Dim FirstEmptyRow As Long
        FirstEmptyRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row

        Sheets("Sch_Pricing").Range("B2:M16").Copy
        .Cells(FirstEmptyRow, "A").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Sheets("Sch_Pricing").Range("B17:M37").Copy
        .Cells(FirstEmptyRow, "FJ").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
       
        Sheets("Material").Range("B5:M152").Copy
        .Cells(FirstEmptyRow, "R").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

        Excel.Application.CutCopyMode = False
    End With
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
   
End Sub

Hey Joe!

So this brought total calculation time down from 2m 45s to 1m 07s, so more than half. Definitely an improvement, and it's at least usable now. Not ideal, as I'd like it to be in the 10-15s range, but it gets me closer. Any other thoughts as to why it's taking so long? (It's not a PC or resource issue)

Thanks again!

 

[Joe4]

Hey Joe!

So this brought total calculation time down from 2m 45s to 1m 07s, so more than half. Definitely an improvement, and it's at least usable now. Not ideal, as I'd like it to be in the 10-15s range, but it gets me closer. Any other thoughts as to why it's taking so long? (It's not a PC or resource issue)

Thanks again!

There could be a number of factors (size of file, amount of data, the number of formulas, other VBA code, if you are connecting over VPN, etc).
Without knowing these details of having access to your file and environment, it is difficult to say.

Have you tried stepping through your code one line at a time using the F8 key and see if you can detect where the slowness might be occurring?

 

[Nordicrx8]

There could be a number of factors (size of file, amount of data, the number of formulas, other VBA code, if you are connecting over VPN, etc).
Without knowing these details of having access to your file and environment, it is difficult to say.

Have you tried stepping through your code one line at a time using the F8 key and see if you can detect where the slowness might be occurring?

 

[Nordicrx8]

It's a fairly small data set, and technically it's just copying over text. (formulas are pulling in text in a separate step) No VPN is required as data is come straight from the main tab.

Basically data is being pulled from the main tab and the material tab, updated, then sent back to the main tab. I even fully rebuilt the sheet to rule out any glitches with the sheet, as that happens sometimes.

I don't think there's anything proprietary/confidential about the data, so I uploaded it to my google drive. Link is below, if you wanted to download a copy? Link to File

Really appreciate all your help!!

 

[Joe4]

OK, I took a look at the file, and manually stepped through it, like I recommended doing in the last line of post 5, and found where it is hanging up.
It is on this line:

    Application.Calculation = xlCalculationAutomatic

This is the line that turns automatic calculations back on.

So, it seems that to be having issues doing calculations after the data is copied.

 

[Nordicrx8]

OK, I took a look at the file, and manually stepped through it, like I recommended doing in the last line of post 5, and found where it is hanging up.
It is on this line:

    Application.Calculation = xlCalculationAutomatic

This is the line that turns automatic calculations back on.

So, it seems that to be having issues doing calculations after the data is copied.

Oh - I guess I wasn't fully following you on how to step through. I was pressing F8, and it was highlighting each line, but I didn't know how to test each line.

this totally makes sense - on the SCH_Pricing & Material Tab, there are formulas all the way to the right that are indexing data:

=LET(f,INDEX(FILTER(Main!$R:$GZ,(Main!$A:$A=$B$1)*(Main!$C:$C=B$4),""),ROWS(BC$5:BC6)),IF(f="","",f))

=IF(INDEX(Main!$E:$E,MATCH(1,(Sch_Pricing!$B$1=Main!$A:$A)*(Sch_Pricing!B$4=Main!$C:$C),))=0,"",INDEX(Main!$E:$E,MATCH(1,(Sch_Pricing!$B$1=Main!$A:$A)*(Sch_Pricing!B$4=Main!$C:$C),)))

these formulas are in each cell, pulling in the data. the Load SO button then copies the data into view so it can be edited, then the update master button takes the data and pushes it back to the main file. I guess the formulas are just a lot to calculate, and there's not much I can do to get around that one.

 

[Joe4]

Oh - I guess I wasn't fully following you on how to step through. I was pressing F8, and it was highlighting each line, but I didn't know how to test each line.

Yes, if you press F8 it will run that line of code, and when it finishes, it will move to the next line of code and highlight it yellow (letting you know it is ready to run that line of code now).
So if you continually press F8, you can see how each line of code takes less than a second until it gets to that one, and "hangs up" for a while.

Those are some pretty intense functions, but I don't think you are helping matters by using entire column references, like "Main!$A:$A".
If you could narrow all those complete column references done to just use the actual range with data, that should improve performance too.
However, you will probably need to do that dynamically through VBA to first locate the last row with data, and then write those formulas.