How can improve the speed of macro run time



I am looking to improve the speed run time for said attached macro.

1. Is there any solution (any coding) to improve macro run time to the fastest speed and complete the run time within no time i.e. just a fraction of second even for large data.

2. Also, Is there any solution for when the macro is running in one excel workbook, the another excel workbook get hanges for time till the macro done with run time (i.e. shows buffering till the macro run).

Please guide for the solution.

Looking for your valuable guidance.




Selected Answer

Hello Sunil,

You seem to have only 200-odd rows of data which shouldn't make your codwe particularly slow. It should get done in under a second. If it takes longer that might be because the timer that updates the data is faster than the timer that updates your results. Look into the how the two timers are synchronized.

If you want to speed up the code you should wrte all the results to an array and paste the array to the sheet. That will be faster than writing each result to the sheet one by one but I wouldn't help with this beyond pointing you in the right direction. Some input of your own is necessary.

As for your other question, that is another question. But from where I sit it seems that your problems are caused by the combination of macros and possibly other autmation projects you have running, not by any one macro. Therefore a soltion may not be possible with the tools we have on this forum.



Hi Variatus, 
Thanks for your response. 
The data is beyond 500 rows. I have mentioned for example purpose.
Is it possible you to help me here.
Can I request you to please help me with the coding to write the results to an array and paste the array to the sheet for attached excel sheet. 
It would be a great help.
SunilA (rep: 34) Jul 21, '21 at 10:30 am
Add to Discussion


On your first question, I'm not sure why you're obsessed with speed but apart for disabling screenupdating etc, for big data sets (yours isn't really) using arrays can help, as Variatus says.

You can look at Don's tutorials e.g. Loop Through an Array in Excel VBA Macros or other questions in the Forum (e.g. my answer/file to the previous question sum and merge duplicated data).

The second question is difficult to answer, since we're not sure what you're doing and for the reasons pointed out by Variatus.


Answer the Question

You must create an account to use the forum. Create an Account or Login