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

 

[Nordicrx8]

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.

Ah, I'm following you now! This is super helpful - thank you! When I was pressing F8, it was going through the lines so quickly, I didn't think it was doing anything. I don't notice it was actually performing the actions.

At this time, I have to use broad column references as I'm not sure how large this sheet will grow, but I'll see if I can trim it down at all. Though I assume most of the delay is from the double multiple index formulas. At least now I know what's causing it, and I can speak to it! Thanks so much for your help - I really appreciate it!!

 

[Joe4]

You are welcome.

At this time, I have to use broad column references as I'm not sure how large this sheet will grow

That is why I was suggesting to dynamically update/create those formulas in VBA after you do your transpose copies.
VBA can easily determine exactly where the data ends, and then you could apply that to the formulas.

Just another option to consider...

 

[Fluff]

As well as limiting your ranges you could also use

=LET(f,FILTER(Main!$E2:$E10000,(Sch_Pricing!$B$1=Main!$A2:$A10000)*(Sch_Pricing!B$4=Main!$C2:$C10000)),IF(f="","",f))

which should improve the performance.

 

[Nordicrx8]

You are welcome.


That is why I was suggesting to dynamically update/create those formulas in VBA after you do your transpose copies.
VBA can easily determine exactly where the data ends, and then you could apply that to the formulas.

Just another option to consider...

I'm not the strongest with VBA yet, but I stumble my way through by piecing together code I find online and coming here when I get stuck. I have a meeting with my boss in an hour - if the delay is a deal breaker, I'll spend some time this weekend trying to figure out how to add this into the code!

 

[Joe4]

Take a look at fluff's simplification of your formula too. That may save you some time.