How to optimise counting in loops

Hi, so I have a macro that takes an hour to process and I have an idea why but I do not know how to optimise it. If anyone can, can you please help?

Thank you!

Please Login or Register  to view this content.

Come on Goede7...Get with the program...lol

By now you should know that an sample upload speaks a thousand words...

Difficult to know exactly what your macri is doing, but are you suer you shoud be looping a macro anyway. This subject really should be a FAQ. I see it time and time again and looping just isn't the best way of doing many of these things.

Whenever you find yourself creating looping code stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop.

The fastest, most efficient and elegant way for this sort of stuff is to use an Autofilter to filter the rows you're interested in.
Then use syntax like

Please Login or Register  to view this content.

then paste to your destination cell.

This uses just one Copy / Paste instruction rather than many if you're looping through many cells.

You may need a heper column that contains a function which will identify the rows you're interested in so taht you can use the Autofilter. te macro could of course generate that helper column.

Originally Posted by Geode7

Once I manage to figure out how to upload the unloadable file, then you will realise why.

What's the size of your file. We don't need zillions of rows a representative sample would do.
The forum permits up to 9.6 Mb for .xlsb files rather than the ~1.2Mb for the rest


To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

I'm not sure how many iteration you are performing at below section. But in general avoid use of Redim Preserve. Adding item to collection will be much faster than using Redim Preserve at each iteration.

Please Login or Register  to view this content.

Though difference wouldn't be significant unless you have 100k loop iteration or more (at 100k iteration, difference is about 1/2 sec) for simple loop.
Also, consider using Instr() vba function rather than Application.Find.

So, I tried other macros and this works far faster. However it still takes 1 and a half hours to complete it. Is there a way to optimise this loop?

Please Login or Register  to view this content.