Excel 2007 Read unique values from a range into an array

I'm trying to read unique values from an Excel 2007 named range into a VBA array,  I've figured out how to do this when reading from an access DB table but that's not an option here.

For example...

If I have a named range on Sheet1 in cells A1..A6 called Fruit
and Fruit contains:
Apples
Apples
Oranges
Peaches
Kiwi
Kiwi

the array should contain:
Apples, Oranges, Peaches, Kiwi

Any help would be greatly appreciated.

Thanks
CEB0827Asked:
Who is Participating?
 
Patrick MatthewsCommented:
CEB0827,

Must it be an array?  Arrays are tricky when it comes to distinct values; Collections and Dictionaries are much easier in that regard.

Here is a Dictionary approach:



Dim dic As Object
Dim LastR As Long
Dim Counter As Long
Dim arr As Variant

With ActiveSheet
    LastR = .Cells(.Rows.Count, "a").End(xlUp).Row
    arr = .Range(.[a2], .Cells(LastR, "a")).Value
End With

Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare 'vbBinaryCompare for case-sensitive matching

For Counter = 1 To UBound(arr, 1)
    dic.Item(arr(Counter, 1)) = arr(Counter, 1)
Next

Open in new window


At that point, your Dictionary has the distinct values.  If you must have the array, add this at the end:

Dim DistinctValues As Variant
DistinctValues = dic.Keys

Open in new window


For more about the Dictionary class:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html

Patrick
0
 
CEB0827Author Commented:
Works perfectly, cant thank you enough, I hate working with arrays & now I have a better way to do this.
0
 
Patrick MatthewsCommented:
CEB0827,

Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_3391-Using-the-Dictionary-Class-in-VBA.html
and click 'Yes' for the 'Was this helpful?' voting.

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.