Which would you prefer?

Hey All,

While i was replying to so many VBA related threads, a question struck me and i couldnt decide which was better among the two. I thought of putting forth this question to all of you so you can provide your opinion.

If you receive a question wherein the user would like to transfer the contents from one master sheet to several other sheets, split by for eg. month / date. Would you choose to use the autofilter method (get the list of unique dates and then filter by each date and copy to the respective tab) or would you go row by row through a loop and then copy the row to the appropriate tab?

While running the code, which is more efficient and faster? Is it the autofilter or the row by row method?

Need your inputs.

Thanks everyone....have a nice day !!!

Hi arlu1201

The filter would be faster .. But if you are writing the code its your call . No right or wrongs just is.

all loops are slow

I'd choose the filter approach, though I think would be an Advanced filter rather than Autofilter.

Regards, TMS

Autofilter is more efficient to my knowledge. The only problem you can have is if the filtered data results in a range reference that is too complex to copy.

Dom

I couldnt decide which was better among the two

There are probably more methods to get the same results:

- a databasequery
- putting the data into an array, filter that in memory and writing the results to separate worksheets

If you post a sample file we all can do some testing and compare the results.

What is a filtered disconnected recordset?

Have a look over here.

I'd suspect arrays would be faster than filters.

A little of both. I have seen similar questions where arrays have proven faster especially with large datasets. Here I think you would need to preprocess the data to get a list of dates to filter by and if you're going to iterate them anyway, I suspect it would be faster to do the work as you go (not copying row by row obviously!).
However, unless speed were critical I would probably actually use the autofilter method for simplicity.