Speed up a macro

**jomili** · 01-11-2010, 02:13 PM

I've got a rather involved macro that's running kind of slowly, and I would appreciate any help I can get speeding it up. It's in two parts; the first is to create and email a report, the second is to format so it's pretty for printing. The full codes for both routines is pasted below.

The email part I developed first and it runs pretty quickly. Afterwards, I added the second macro, which is called halfway through the first.

Stepping through the code in the second macro, the problem I see is in this section, the setup for setting the heighth of merged cells in the report:

Please Login or Register  to view this content.

Stepping through it, it seems to repeat hundreds of times, and seems to really bog down the response time.

Here are the two macros. The problem (what I see as the problem) is presented first, the overall email macro presented second. I appreciate any help you can provide on this.

Please Login or Register  to view this content.

Here's the Mail routine:

Please Login or Register  to view this content.

**royUK** · 01-11-2010, 02:16 PM

Loops will always slow down code, formatting code tends too beslow.

It's good practice to avoid merged cells

**jomili** · 01-11-2010, 02:24 PM

I'm very aware that merged cells are a no-no, but sometimes there is no choice.

In debugging code, if I know a loop is going to slow it down but I also want to check the rest of the code, is there a way to have Excel run the loop only, so I don't have to press F8 a million times?

**Leith Ross** · 01-11-2010, 03:07 PM

Hello Jomili,

The loop is slow because it is testing each cell in the UsedRange. If the Merged cells only occur in specific columns, the code could be sped up by checking only those columns.

**jomili** · 01-11-2010, 03:13 PM

Leath,

Thanks for chiming in. The macro looks at rows, not columns (I HOPE!).

The overall process is like this:
Run test, paste results into sheet (unknown number of lines)
Past the solution
Skip a line
Run the next test, paste results into sheet (unknown number of lines)
Past the solution
Etcetera

So I don't know how many lines the finished product should be, nor how many or which lines will contained the merged data.

**Leith Ross** · 01-11-2010, 03:24 PM

Hello Jomili,

If you could post the workbook for review, we could provide you with a better answer.

**dlh** · 01-11-2010, 04:05 PM

Originally Posted by jomili

In debugging code, if I know a loop is going to slow it down but I also want to check the rest of the code, is there a way to have Excel run the loop only, so I don't have to press F8 a million times?

You can put a stop-point after the loop. Click in the left margin next to the first statement after your loop; a brown bullet appears and the statement is highlighted. Then press F5; your entire loop will run. Then continue pressing F8 to step through your code.

**jomili** · 01-11-2010, 04:50 PM

dlh,
Thanks for the tip. That's a good one to know.

Leath,
The last time I was asked to, and did, post my workbook, I recieved a very rude note telling me that no one was going to look through all of that code. That's why I only posted the pertinent sections before.

However, since you requested, please let me FIRST say I do NOT expect you to check all of my code, as this thing does a lot, and it's only in the Mailing section that I'm having trouble. Right now the Checklist is solving 5 steps in a 10 step checklist, so it will grow larger, and I want to make sure it's running as fast as it can.

With that said, here are 3 workbooks. Open ESS or APR first (both need to be open), then open the Checklist and follow the instructions. Both ESS and APR have been severely stripped down; the APR normally has about 20,000 lines of data, so it's tiny now, thought it's still a large workbook. Feel free to ask me any questions.

Thanks a lot!

**jomili** · 01-19-2010, 12:41 PM

I'm still in need of help on this one.

**broro183** · 01-19-2010, 02:41 PM

hi Jomili,

DLh's suggestion of pressing in the left margin creates a breakpoint. These only exist for the time the file is open. An alternative is to type the word "Stop" on a line by itself immediately after the loop & this will always be there (if you save the file) until you delete it.

Have you attached your workbooks?
(they aren't showing up on my screen)

To add to the comments about limiting the range searched, I recommend moving the "Redim" out of the loop because this can slow the macro down. It may not help at all but you won't know until you try. To do this "do a dry run of the loop" to identify how large the array needs to be, Redim ONCE using the correct size, then process your loop again with actions.

hth
Rob

**jomili** · 01-19-2010, 04:19 PM

Sorry, apparently my mind is going; I THOUGHT I'd posted my workbooks. Here they are, and there are 3 of them in the Zip file. Open APR and ESS first, then Checklist. Checklist will run through the whole procedure and you'll be able to see where the slowdown is.

While I understand a little of what you're saying about redim, I'm not sure how to carry it out, so would appreciate a bit more help on that.

Thanks!

**quekbc** · 01-19-2010, 06:16 PM

A shortcut to quickly run over loops and other subroutine is the hotkey Shift+F8.
If you accidentally stepped into a loop or call a different subroutine and you wish to get out, press Ctrl+Shift+F8 to skip over the loop or jump back into the original procedure.

The loop will still be run, but it won't break at any point within the loop.

Another good hotkey is Ctrl+F8. Where you can place the cursor at the line of interest, press it, and the code will run and break at the line where the cursor is.

**broro183** · 01-20-2010, 08:21 PM

hi,

I'm not sure if my way of moving the redim out of the loop will help or not (esp as I have just done it by duplicating the loop!*), but I have made a few other changes including the removal of a .select which will have been slowing the code down. Note, I have not tried testing my changes, I've just made changes as I read through from top to bottom.

*If you find a way to very quickly count merged cells, that would be a better method.

hth
Rob

**jomili** · 01-21-2010, 12:05 PM

Thanks for the attempt at cleaning up my code. I went through all your changes, and corrected and changed a few things in mine, but the new routine you put in for the Merged Cells is still not working. Here's that particular code as it now stands:

Please Login or Register  to view this content.

You had noted out the line

Please Login or Register  to view this content.

but I had to put it back in to get it to run. Now it's giving a run-time error code 1004 Application-Defined or Object-Defined error at this line:

Please Login or Register  to view this content.

**broro183** · 01-21-2010, 09:50 PM

hi,

Sorry about that, my previous attempt was shocking - I should have tested it.

The reason the With statement was failing was due to the string array not being populated because I hadn't reset the Set ranges (after the first loop), therefore the tests were all failing. Also, I found out from the Help files that the mergearea needs a single cell reference.

I've had another go & have tested it this time :-)
btw, when I run through it I'm getting an error as the PT is created but if I press Debug & then [F5] the code finishes running as normal.

To help speed this code up even more (assuming my changes have done anything?), I would try testing for the number of columns in a merged section, skipping over them & then processing the next non-merged cell (perhaps a Do ... Until loop for each column wrapped inside a Do... Until loop for each row).

hth
Rob

**jomili** · 01-22-2010, 11:46 AM

Rob,

They've locked us down here at work, so I can't see your zipped file (I couldn't even if it was unzipped). Could you just post the pertinent code changes?

Thanks for all your help.

**broro183** · 01-22-2010, 02:36 PM

hi Jomili,

I made a few more changes in other sections of the code too (in case you do get a chance to download it) but here's the Format macro...

Please Login or Register  to view this content.

I removed the preceding loop (see code comments) - hopefully the impact of creating an array of this size is okay while the elements are empty. Once the the loop has completed the array is resized.

I made one other change in the NewTry macro because I show file extensions on my computer:

Please Login or Register  to view this content.

hth
Rob

**jomili** · 02-17-2010, 05:05 PM

Rob,
I apologize it's taken so long to reply. The code changes you made really did the trick. It went from a 45-second timeframe to about 10 seconds. Thank you so much!

You asked a few questions in your code that I couldn't answer, as I did not create the original code, so am having a tough time following everything that's happening. You said:

Please Login or Register  to view this content.

but I'm not sure what test you're referring to. You also asked:

Please Login or Register  to view this content.

but I don't know if I'm using the loop or not, so am uncertain how to proceed.

Again, thanks so much for your help!

Speed up a macro

LinkBack

Thread Tools

Rate This Thread

Display

Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Re: Speed up a macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions