Removing Duplicates from listboz

[chrisjames25]

Hi All

I have a userform with a couple of listboxes. ONe of the listboxes rowsource is linked to a dynamic range. This range contains duplicates. For example the range currently has 10 apples in it and 5 bananas but in the listbox I only want to see apple and banana once.

Any ideas how this can be achieved

[Solus Rankin]

Instead of using the rowsource property, try placing this in the initialize event for the userform. Just change RowSourceDynamicRange to the dynamic range of items you'd like to use. If you would like to tell me what the range is I could edit for you.

Please Login or Register  to view this content.

e/ I forgot to mention you might have to change the name of the listbox if its not ListBox1

[chrisjames25]

Hi

Thanks for your reply. THe dynamic range depends on an entry in another listbox so this is the code I have tried to adopt into your. However it is not working. Any idea where I am going wrong

Please Login or Register  to view this content.

cheers for help

[Solus Rankin]

So the sheet is selected by the listbox named categorylst, and the range on the sheet selected (by the listed box) will D6:Dx?

[chrisjames25]

yep that is correct.

[chrisjames25]

ok It now seems to be removing the duplicates however as I flick through the selections in categorylst it keeps adding to the rowsource for genuslst. What tweak wud I need to make to reset the genus data each time the category lst is changed?

[Solus Rankin]

This is untested without a sample worksheet but you cant try:

Please Login or Register  to view this content.

This should still be in the userform's initialize event.

[chrisjames25]

Hi

Unfortuantely it appears to work once or twice when I change the categorylst value but then it crashes and gives the following message:

Run time error -214717848 (800 10 108):
Automation error
The object invoked has disconnected from its clients

Any thoughts on this would be massively appreciated

[Solus Rankin]

Can you redact any sensitive information from your workbook and post it as an example?

I won't be able to recreate that error.

[Kyle123]

I'd do it like this, using AddItem is slow and reading/writing to the worksheet should be done in bulk:

Please Login or Register  to view this content.

[chrisjames25]

I'd do it like this, using AddItem is slow and reading/writing to the worksheet should be done in bulk:

Please Login or Register  to view this content.

Hi Sorry for being Stupid but am I supposed to adjust the "Scripting.Dictionary" to something. Also where should I put this code. I put it in the Sub Categorylst_change() but it had no effect.

THoughts much appreicated

[chrisjames25]

Ok I am understanding your code a bit more but struggling to get it to work. ANy chance you could explain it line by line then I can see if there is a simple reason why when I have put it in it has not worked.

Cheers