Remove duplicates from 1 dimensional array in VBA

Is there a quick way to remove duplicate entries from a 1 dimensional array?

Say I have an array of 1,000 entries and elements 150 and 200 are already listed elsewhere in the array. How would I remove these two lines and return the array with a new size of 998 elements?

Finding the dups appears straightforward enough, simply sort into order and work through the array checking the lines with the previous one. It's the removing and resizing (or redimming) that's got me stumped.

Thanks

You should be able to modify this code which populates an array from a named range ( one column wide) "input", extracts the unique values and writes them a range named "output".

Please Login or Register  to view this content.

Originally Posted by zaphodb2003

Finding the dups appears straightforward enough, simply sort into order and work through the array checking the lines with the previous one. It's the removing and resizing (or redimming) that's got me stumped.

After sorting, go through Array1 and copy the non-duplicates to a second array (Array2).
Array2 is dynamic and is dimensioned to the same size as Array1.
Each time a duplicate is found reduce the size of Array2 by 1.
Optional - when finished resize Array1 and assign Array2 to Array1.

Yes you could take that approach and use two arrays .
The code I posted uses a collection to filter out duplicates.
Using a collection will tend to simplify your code.

Yes, a collection would be a better choice to prevent duplicate values from being stored.

What's happened to the OP? No response.

We'll just talk among ourselves.

I used the dictionary to remove dupplicates.
I have a list of students who failed and I wanted a list with all the subjects, as many students failed the same subjects I had to remove dupplicates. I worked from the worksheet without using an array, but it is easy to adapt this code by writing the info in an array first and after send the array to the function.
It's a code snippet I don't use regularly, so I didn't optimise it, my excuses , it's a draft "as it is"

Please Login or Register  to view this content.

'Note: You will need to add a reference to "Microsoft Scripting Runtime" as the code uses its Dictionary object.
'To do this, select Tools from the toolbar, then select "References", and then select "Microsoft Scripting Runtime"

Good luck