Execution Speed of VBA Code

[sdelaney7]

Good Day

I have a question regarding being able to improve the speed of the following code:

Please Login or Register  to view this content.

Is there a way to accelerate the execution by rewriting it somehow?

Thanks in advance!

[Mordred]

How long could that code possibly take? There doesn't seem to be much going on there!

[sdelaney7]

It takes about 5 to 10 seconds to execute the collapse. There is approximately 100 groupings that need to be expanded or collapsed when the code is triggered. The expansion runs 3 to 4 times faster than the collapse.

[Leith Ross]

Hello sdelaney7,

If you are having speed issues with your code then there is probably more going than this Sub. There really isn't anything you can do to achieve a significant speed improvement with this Sub. VBA can not speed up the process of expanding and collapsing the rows. Unprotecting and protecting the worksheet takes no time at all. I would suggest you post a copy of the workbook for review and then we can offer more practical advice.

[sdelaney7]

I have attached a redacted version of the file for your review. Thanks for the response!

[Leith Ross]

Hello sdelaney7,

That's insane! It took about 15 seconds to collapse on my machine. The only thing I can suggest is to reduce the number of groups you are collapsing, if possible. Doing smaller chucks will be faster. Since this function is built-in, there is no other way to gain speed improvement.

[sdelaney7]

My sentiments exactly (but your response used no expletives and my thoughts contained many). I'd like to pick your brain on this some more though if you don't mind. If sheet calculations are set to manual, the process of expanding and collapsing groups is almost instantaneous. Is there a way to code for the calculations to be set to manual upon execution and then revert to automatic once complete? This would be an acceptable workaround given the way the users interface with the workbook.

[sdelaney7]

I solved the problem by manipulating whether or not automatic calculations were enabled in the code.

[Leith Ross]

Hello sdelaney7,

That's interesting. I did not look for any formulas that were being calculated which of course would slow things down. Thanks for pointing that out. Glad you got it solved.