No duplicates from range to an array

i have a range with names in. i.e
Set VALIDarea=Range"B3:C29,E3:F29,H3:I28,H3:I29"

what i need to do is store as an array all names i.e name$(1)=john
name$(2)=susan ....
But i need no duplicates. How do you count in vba all different entries in a
range.

Please help

Sunil

Sunil,

This sub uses the fact that adding a duplicate key to a collection causes an
error. The error is bypassed and the duplicate is not added. The
collection is cleared at the end. Replace the debug.print line with your
processing:

Sub test()

Dim cell As Range
Dim no_dupes_coll As New Collection
Dim i As Long
Dim names_array()

For Each cell In Range("B3:C29,E3:F29,H3:I28,H3:I29")
'attempting to add a duplicate key causes an error, so ignore
'and the duplicate will not be added
On Error Resume Next
no_dupes_coll.Add Item:=cell.Value, key:=cell.Value
On Error GoTo 0
Next cell

If no_dupes_coll.Count > 0 Then
ReDim names_array(1 To no_dupes_coll.Count)
For i = 1 To no_dupes_coll.Count
names_array(i) = no_dupes_coll(i)
Debug.Print names_array(i)
'delete above line and do your processing here
Next i
End If

'clear the collection
Do While no_dupes_coll.Count > 0
no_dupes_coll.Remove 1
Loop

End Sub

hth,

Doug

"Sunil Patel" <sunil@sunil.freeserve.co.uk> wrote in message
news:da6bvt$otn$1@news8.svr.pol.co.uk...
>i have a range with names in. i.e
> Set VALIDarea=Range"B3:C29,E3:F29,H3:I28,H3:I29"
>
> what i need to do is store as an array all names i.e name$(1)=john
> name$(2)=susan ....
> But i need no duplicates. How do you count in vba all different entries in
> a range.
>
> Please help
>
> Sunil
>