Adding ranges with VBA code depending on Form Control

Hi all,

Is there a particular reason why below code runs for 25 seconds? Can it be optimised somehow? The range aren't calculating anything, so far I have simply "added" them to the workbook...

Please Login or Register  to view this content.

Why are you giving the same ranges two names?

Because if the fiscal year 2016 is selected in my form control combobox, I need last years data to refer to the same as this years data as 2015 data is unavailable.

and you may want to make calculation manual while you add the names and, if the TimePeriod_Analytics combobox is directly linked to a range using ListFillRange, you should probably use a flag variable to prevent recursion just in case.

Not sure what you mean by "flag variable"? My combobox cell link is the "Fiscal_Year_Analytics" range as defined in my code.

The list in the combobox is coming from my "Control" sheet, the cell highlighted in yellow = Worksheets("Control").Range("Fiscal_Year_Analytics")

Did you try turning calculation to manual to see if that speeds things up?

Yup, just tried. If I use "Application.Calculation = xlCalculationManual" alone, it speeds it up to about 3-4 seconds (instead of 25 seconds). However previously I used my "optimise code" macro (see below) and it did not speed up anything...hmm
3-4 seconds isn't too bad, but not ideal, as I still need to add some code. But I assume there's no more room for speed improvement?

Please Login or Register  to view this content.

forum.PNG

it's a static address that doesn't change!

Please Login or Register  to view this content.

But I need to turn automatic calculation back on at the end of the code??

Oops! Thanks for pointing that out! It's working now! I guess I have to live with the 3-4 seconds delay. Thanks for your help!