Loop or use autofilter to get my results

**bopsgtir** · 01-13-2013, 06:21 AM

Hi all, just a quick question.

I have a sheet that opens with a userform which has a textbox for you to imput your search criteria.

I then want my code to look through three closed workbooks and everytime it finds my criteria copy that line and paste it onto my search sheet. my question is what would be the best way of achieving this. would I loop through every row and when I find it copy and paste or would I use the autofilter. the 3 closed workbooks would have around 100k rows of data each.

**Richard Buttrey** · 01-13-2013, 06:26 AM

Without doubt use autofilter. It is the fastest way I know to achieve what you want.

As a general principle always use standard Excel functionality (if necessary in a macro) rather than writing looping code. There is a big time overhead with loops since each time through a loop VBA has to jump into Excel, do something and then jump back, all of which takes time. This can be mitigated somewhat by reading a range into a VBA array and processing the array before writing the answer back to Excel but stick with autofilter.

**Alf** · 01-13-2013, 06:27 AM

Autofilter would be the quickest way to get the data.

Alf

**bopsgtir** · 01-13-2013, 06:27 AM

Thanks Richard, that makes a lot of sense.

**Winon** · 01-13-2013, 06:31 AM

Hi bopsgtir,

the "Loop" could end up with over 300k of rows, and it would be VERY SLOW.

Rather go for autofilter if you can, and copy filtered Range.