Need Help Optimizing Some Code

**jackb1117** · 11-12-2009, 03:38 PM

Hello all,

I have a bit of code that takes a long time to run, and I was wondering if there was a way to optimize it to run faster (or a different approach to what I am doing, as I am relatively new to VBA), or if it is the number of records I am hitting against that is slowing this down.

Cliff notes on what I am doing, I have multiple sheets with ~ 55k records on each. I want to search all of these sheets (the code I will post below is only searching the first two) for all rows that match a certain criteria, and if a row matches, move it to a summary sheet (Results). This is what I have so far (it works, but very slowly):

Please Login or Register  to view this content.

**JBeaucaire** · 11-12-2009, 06:30 PM

You should be able to AUTOFILTER each sheet and copy all the matching value rows in a single mass copy command, that is if each sheet represents one big continuous data set. Does it?

If so, turn on the Data > Filter > Autofilter for one sheet and try filtering column I for one possible value. Does it show all matching results from that sheet? As long as there are no fully blank rows in the data, it should.

If that works, let me know, I have several AutoFilter macros that would adjust easily for this.

If that doesn't work, can you post up a sample workbook with a few sheets of data so I can see if there's a better way?

**jackb1117** · 11-12-2009, 07:20 PM

Thanks for the reply. I actually started going down the autofilter road at first, then decided to switch to using the .find and .findafter approach. Do you anticipate using the autofilter approach would be much faster? I would definately be interested in the autofilter macro you referenced (and yes this is continous data, so it should work). Thanks!

J

**JBeaucaire** · 11-12-2009, 07:50 PM

You can write this yourself then, probably. Record a macro of you:

1) going to the first sheet
2) turning on the autofilter
3) filtering the data by a value
4) Copy all the visible data and paste it enmasse to the report sheet
5) Turn off the autofilter

Then come back here and paste up your recorded macro, we'll show you to convert it to a "all sheets" macro.