Very Slow Macro... Any Advice How To Speed It Up, Please?

[mrodrigues]

..........

[Jacc]

Well, the good news is that this code has lots of potential for improving speed.

Here is some reading for you:
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm
http://blogs.office.com/b/microsoft-...practices.aspx

I haven't studied your code very carefully so I might be off a bit here. And it's the end of the day...
I give you some samples here to start with, hopefully someone else will help out as well.

This:

Please Login or Register  to view this content.

could be replaced by this:

Please Login or Register  to view this content.

One of the first things is to avoid Select and Activate as much as possible. Yes, the macrorecorder loves that stuff but it really hurts speed.
Example:

Please Login or Register  to view this content.

replace with:

Please Login or Register  to view this content.

Yet another basic thing is to declare all your variables. You've done a good job so far but here is a tip.
In the VBA editor, Tools menu -> Options... -> Editor make sure that the Require Variable Declaration box is checked.
This will automatically put a Option Explicit on top of every new module and this in turn will give you an error if you forgot to declare a variable. Will help speed a little bit and eliminate some boring faultfinding of variables with the wrong name.
You should manually type Option Explicit on top of your module since it's already there. This will find the variables you forgot to declare.


Copy paste in VBA should be avoided in general and copy pasting entire rows or columns is really not a good idea.

Well that was a start, I'm confident other people here will give you more hands on advise. I see a loop that someone most likely will eliminate...

[mrodrigues]

..........

[mrodrigues]

..........

[rylo]

Hi

You seem to be bringing in all the items from column A whenever you search for an item. Do you really only want to bring in the items when the Component Type is DUT, CAP or RES? If so, then advanced filter will be much faster.

rylo

[event21]

Hi -

If you can post a few sample of the results you need then it might help.

Regards,
Event

[mrodrigues]

.............

[event21]

Hi -

How about this one?

Please Login or Register  to view this content.

Regards,
Event

[mrodrigues]

...........

[mrodrigues]

..........