Excel macro getting slower and slower.

Ok, one example sheet as requested:

I didin't test it, but I changed your code to remove the "GoTo" commands and replace them with looping. This is the preferred method, and it is easier to read.

Please Login or Register  to view this content.

Whizbang, you sure are!

That's soooo much faster now, thanks very much. It may still be suffering from the original problem but since it's 1000 times faster now it doesn't matter.

Actually the problem wasn't really looping. I just gave that to you to show you a better coding practice. The problem is that you were selecting cells inside of a SelectionChange event.

So,when you select a cell, it would then select other cells, which prompts another SelectionChange event, which prompts more cells to be selected, which prompts more SelectionChange events, and so on until finally no more cells can be selected (you reach a stack overflow) or some condition in your code stops the selections from occurring.

The only way to stop this is to either avoid selecting cells (which I did in the code I supplied to you, and is another good coding practice) or turn off events with

Please Login or Register  to view this content.

um....that's the 5th line of my original macro.

Oops. Ha!

What I meant to say is...
Uh...
Hey look, a 100 pound cat!

Exit stage left

Hmm... So it was the looping afterall. I didn't know that it was so much more efficient than GoTo. I never used GoTo before, except for error trapping.

It seems I spoke too soon. When I plumb this back into the original spreadsheet it's still getting slower and slower.

New posting of macro, whizbang's code with a few odd bugs worked out:

Please Login or Register  to view this content.

After some more testing, it definitely looks like a memory issue.

The above code works very fast in a 71kb example workbook. If, however, I even have the main workbook loaded (25Mb) and not doing anything then the code in the example workbook runs slow.

Looking at windows task manager though....with just the example workbook loaded excel is using 26k. With the main workbook loaded it's using 72k.

I don't get it.

Gents, thanks for all the help here but I think the problem lies elsewhere.

Luckily, I've been keeping version histories and the version of my workbook I have from the 15th of June seems to run perfectly. Something I did on that day has caused the slowdown cos all versions after that date are slow (not just on the macro above, others as well that previously were running fine have also starting going really slow).

However, removing the items listed in my changelog (oh yes, got a record of every change I've made to this since day 1) for the 15th doesn't fix it. Looks like I'm just going to have to go back to the June 15th version and re-apply all the dev work done for the last 9 days one by one.

P.S. The sound of sobbing you can hear fading into the distance is me going out for a smoke.