Obtain distinct values from array

[mpothen]

Is there an easy/quick way to select only distinct values from an array in VBA (I'm using Excel 2007)?

I'm hoping there's an existing method for doing this, but maybe you have to do it old school and step through the entire array, compare each, etc??

Thanks,
Mitch

 

[p45cal]

Create a Dictionary or Collection and add them one at a time. Neither object will allow a duplicate entry; you should use on error resume next for a collection, but you can use .Exists for a Dictionary.

 

[mpothen]

Thank you! Any major difference between using a dictionary or collection? I'll check them out, thanks. I've never heard of either actually, so this is a new one for me.

 

[mpothen]

Also, this begs a question.. My scenario is this:

I have a cell in Excel with names separated by a semicolon ("Bob;John;Mike;Bob" for example). I need to obtain the unique values.

What I've done is parse and load them to an array using:
Array= Split(Replace(Cells(4, "G").Value, "; ", ";"), ";")

Then I can use your idea to get the unique values.

So the question is, can I load right from the cell in the same way, forgoing the Array (reduce this process by a step)??

Like:
Dictionary = Split(Replace(Cells(4, "G").Value, "; ", ";"), ";")

or similar?

Thanks again,
Mitch

 

[p45cal]

So the question is, can I load right from the cell in the same way, forgoing the Array (reduce this process by a step)??

Like:
Dictionary = Split(Replace(Cells(4, "G").Value, "; ", ";"), ";")

or similar?

I don't think so.