Speed up macro

**Jasrenkai** · 07-19-2010, 07:01 PM

Looking for help speeding up a macro

Hi, sorry to ask for help here was just wondering if anybody had some time to help me with an issue I have.

I use Excel 2007 and Windows 7, and I have this series of macros. The main macro calls different other macros through its general run. It works and everything just fine but I really need to make it faster, as the size of the data that it runs over can be VERY large and can take some time to run. I have read about a number of things that haven’t really helped a lot, like disabling events, screenupating, page breaks, status bar etc. Some of them didn’t help at all, some of them did in certain parts so I /think/ I have those covered.

The main problem I have with it is all the information varies….the size, columns/rows, can be very large very small, numerous. Anyways if anyone sees any ways to speed it up significantly please let me know! And thanks for any possible help for a first time poster!

Please Login or Register  to view this content.

**JBeaucaire** · 07-19-2010, 07:09 PM

Tip #1 - remove the STATUS BAR coding, updating and displaying status reports greatly reduces speed.

**Jasrenkai** · 07-19-2010, 09:24 PM

Thanks JBeaucaire, I really appreciate your response.

I had read the same thing, and tried it out and it didnt /seem/ to make any difference. Since you mentioned it I went ahead and tested it, first I ran it twice with two matrices and got the times of 34 and 35 seconds.

Then I took out the :

Please Login or Register  to view this content.

And added at the begining and end:

Please Login or Register  to view this content.

I got 47 seconds and 47 seconds on the same two matrices.

Then reverted back to the original and ran it again and got 36 and 35 seconds.

So I am not sure exactly how it will respond to 100 matrices versus 2, maybe it would make it faster with a bunch more to process but with just a few it seemed to make it take longer which is weird. Let me try it with no Status Bar code at all.

Edit: With no status bar coding at all (not eveing making it false and true, just nothing) I got 45 and 47 seconds. Then I thought to myself maybe storing x and mycol in the first part but not really doing anything with it made it slow down. So I took out x = x +1 and all the MyCol lines and got 46 and 47 seconds. Went ahead and reverted it back to original code and got 36 and 35 seconds. =(