Macro keeps causing excel to freeze

Hi

I have the following macro and when I run it keeps causing excel to freeze up saying not enough memory. I closed all other excel sheets to reduce but still freezes up.

There are over 40,000 entries it trying to compare. Is there anyway I can change it so it dont keep freezing up.

Thanks

Please Login or Register  to view this content.

Hi !

Maybe Excel is not frozen but just working as a VBA loop is slow …
Adding a DoEvents codeline before Next may cause Excel to not freeze but could run a bit slower.

Rather than loop and put the formula in each time I'd put the formula in all cells at once then loop through the results.

Without a sample workbook this is not tested but try this:

Please Login or Register  to view this content.

No idea if it will help but worth a try.

BSB

Originally Posted by Marc L

Maybe Excel is not frozen but just working as a VBA loop is slow

That wouldn't return the 'not enough memory' message that the OP encountered.
A variation of BSB's code revision that eliminates the loop entirely.

Please Login or Register  to view this content.

@jason.b75, Have you tested your brilliant idea on 40k rows? Countifs is very slow. respond to respond?

I made no claim of how good it would be, just offered it as an alternative. Given that we don't have the distribution ratio of those 40k rows it is impossible to judge accurately how well any suggestion will perform. 40k rows in each sheet will be slow with countifs, 40k rows total over both sheets will vary depending on the split, a 50/50 ratio will be slower than a 60/40 or 40/60, which will be slower than 70/30 or 30/70, etc.

Whilst I doubt that it will be the case with this thread, it is theoretically possible that countifs might be faster than your method with the ratio highly biased toward either of the two sheets.

I have noted an error in my suggestion, which is corrected below.

Please Login or Register  to view this content.