Microsoft Office Tutorials and References
In Depth Information
GET AN ARRAY OF UNIQUE VALUES FROM A LIST
In the Advanced Filter dialog, select Copy to Another Location, set List Range
to Sheet1!$A$1:$A$30 , leave Criteria Range blank, set Copy To to $H$1,
select Unique Records Only, and click OK.
The list of unique names (with the header Name) is pasted at H1.
After it is pasted, you can sort the list in alphabetical order, if required.
Alternate Solution: This topic demonstrates two approaches for obtaining
the unique values: one using the Collection object and the other using the
Dictionary object. The two approaches are similar in mechanism in that they
make use of the fact that a collection as well as a dictionary cannot contain
duplicates.
The Collection Object Approach
The code for the Collection object approach is:
Sub GetUnique_Collection() ‘Using the Collection object
Dim SourceRng As Range
Dim UniqColl As New Collection
Set SourceRng = Range("A2:A30")
On Error Resume Next
For Each cell In SourceRng.Cells
UniqColl.Add cell.Value, cell.Value
Next
On Error GoTo 0
ReDim UniqArray(1 To UniqColl.Count)
For i = 1 To UniqColl.Count
UniqArray(i) = UniqColl(i)
Next
‘Optional sort routine can be inserted here
Range("H1").Resize(UniqColl.Count, 1).Value =
WorksheetFunction.Transpose(UniqArray)
End Sub
Part
I
This code creates a new collection, UniqColl , and cycles through all the
values in the list of names, attempting to add each name to the collection.
Notice that the statement:
UniqColl.Add cell.Value, cell.Value
contains two references to cell.Value. This is because the fi rst two arguments
for the Add method are Value and Key . A collection cannot contain duplicate
 
Search JabSto ::




Custom Search