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 !!!
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
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
regards pike
If the solution helped please donate here to the RSPCA
Sites worth visiting;
J&R Solutions - royUK
AJP Excel Information - Andy Pope
Spreadsheet Toolbox
VBA for smarties - snb
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
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
There are probably more methods to get the same results:I couldnt decide which was better among the two
- 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.
Or a filtered disconnected recordset?Originally Posted by snb
What is a filtered disconnected recordset?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Have a look over here.
or better, look here
I've come across using disconnected recordsets before in a VBA book I skimmed through and they seem like they should be really useful, unfortunately I've never come a situation where they would actually be useful :s
Plus I don't know of a way of filling them without looping (or memory leaks) which has always put me off.
Maybe this is their calling - lots of filtering and finding on a dataset! They do look as though they would be much faster than filtering in Excel
Click the * below to say thanks
Girls sleep with guys who use photoshop, but marry the ones who work with Excel
Corduroy pillows: They're making headlines!
Did you mean: recursion
http://www.google.com/search?hl=en&q=recursion
I'd suspect arrays would be faster than filters.
Good luck.
Is that based upon any evidence or your gut feeling?
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.
Good luck.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks