Excel 2007 : Macro - loop slows down

I created a macro that runs an iterative process. The macro basically makes some adjustments to some data and copies the data to a blank row. Then, the macro does the same thing over and over.

There are 13000 rows the macro cycles through and I noticed around row 4000, the macro's speed essentially comes to a stop. I also noticed at row 4000 my computer usage (via the task manager) jumps from 60% to 90%.

I assume it is not a coding issue since the macro runs for awhile, but then hits a wall in the middle of the iterative portion.

Does anyone have ideas as to what is going on?

Welcome to the forum,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

And when you do that, post the code. No one can give you advice on a code problem sight-unseen.

shg, I'll let you have the honors....

IALTO,

We're happy to have you here, and more than willing to help -- provided you first take a few minutes to read ALL the forum rules, and then bring this thread into compliance. For now, that means changing the thread title and editing your post to add CODE tags.

ExpRate is undefined.

As a row variable, i should be declared as a Long, not an Integer.

You're writing to the statusbar in every loop iteration. You could do it every 10th or 100th iteration.

If your changes are causing portions of the workbook to recalculate, you should turn calculation off and calculate only whatever portion you need to within the loop.

You make repeated and unnecessary references to ThisWorkbook, which could be eliminated with a With statement or setting some new object variables.

You've set the three range variables to the same range -- why?

So there's a lot you could do, but I can't tell what the whole procedure is doing, so it's hard to be specific.

How big is range ER?

Without seeing the workbook, or knowing if a loop is even necessary, ...

Please Login or Register  to view this content.

Possibly something like:

Please Login or Register  to view this content.

That exact syntax will work. It doesn't turn calculation back on for that range, but it does calculate it once with all dependencies. However, what you'd actually do, for the reasons previously described, is

Please Login or Register  to view this content.

See Help for the Calculate method.

The code doesn't truncate anything. Change the formatting to General.

Having no idea what's in your workbook, I could only speculate. Maybe you have Precision as displayed ticked.

Sure, that's a good guess. If so,

Please Login or Register  to view this content.