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

Nordicrx8

Board Regular
Joined
Dec 10, 2015
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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
 
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!!
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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...
 
Upvote 0
As well as limiting your ranges you could also use
Excel Formula:
=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.
 
Upvote 0
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!
 
Upvote 0
Take a look at fluff's simplification of your formula too. That may save you some time.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top