Hello all,
My goal is to populate a listbox (listbox3) in a user form with unique text items that appear in Column A of my datasheet. The code below is able to do what I want, albeit very slowly (right now there are 20,000 rows of data I need to select unique items from). Are there any alternatives that might speed this up?
Please Login or Register to view this content.
Think you can attach a sample file?
I'm getting an error with the file upload, but Column A looks like this:
country
US
US
US
US
US
US
US
UK
UK
UK
UK
UK
UK
Italy
Italy
Italy
Italy
Italy
Italy
Italy
Italy
Italy
Italy
Italy
US/CAN
US/CAN
US/CAN
US/CAN
US/CAN
US/CAN
US/CAN
accidental double post...
Last edited by mcdermott2; 04-02-2013 at 03:17 PM.
Try
Please Login or Register to view this content.
Same thing as jindon's except I used collection
Please Login or Register to view this content.
Hi
Both the above examples are good.
However there is a lot of reading and writing from excel to VBA which will slow the process down.
I would read all the column into VBa as an array.
Transfer the unique items to a collection to ensure only unique items exist.Please Login or Register to view this content.
I would then write the Collection back to excel.
As there is only one read and one write in the Macro. It should be really fast.Please Login or Register to view this content.
Thanks! jindon- your code worked perfectly (I got an error because I am in idiot and had changed the tab name), but JieJenn, yours worked like a charm. I really appreciate both of your input!
Last edited by mcdermott2; 04-02-2013 at 03:49 PM.
You might want to develop this formula derived solution.
It uses a mobile named range for the drop-downs in Column E based on the unique values generated in Column D.
As it stands it isn't portable, but this can be addressed if you want to pursue this method.
Column A must be grouped, but not nescessarily sorted.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
my code
1)
is actually looping inside the array, not cells, so it should be very fast even 20000 cells.Please Login or Register to view this content.
2)
If you want the unique in the range. add one line
Please Login or Register to view this content.
This Code should be fast. So Enjoy.
Please Login or Register to view this content.
Thanks Jindon.
I will try your code.
Then you need to check the sheet name, I copied it from your original code.Originally Posted by mcdermott2
mehmetcik's code works perfectly if the "=" is dropped fromPlease Login or Register to view this content.
Yes, my mistake. Thanks for the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks