Bernie,

Thanks for the great links! Chip certainly puts some good stuff on his
site.

However, while I appreciate that much of this is done with worksheet
functions (often the better way), I am keen on a VBA solution. The John
Walkenbach method, ref "Power Programming 2002 VBA", page 423, based on
a tip by J.G. Hussey, published in "Visual Basic Programmer's Journal"
is very neat indeed : it uses a Collection object, and takes advantage
of the fact that the key argument must be a unique text string ….

Set My_List = Range("My_List") ' Note, source is a Dynamic
Named Range

' The next statement ignores the error caused by attempting to add
a duplicate
' key to the Collection object. The duplicate is not added - which
is just
' what we want! Note: the 2nd argument (key) for the Add method
must be a string

On Error Resume Next
For Each Cell In My_List
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell

Done. The items in NoDupes can populate a UserForm, a VBA variable, a
worksheet range … you've got to admit, that's pretty cool!

What's more, the list can be sorted :

' Sort the collection (optional). This procedure works well for
normal lists, but
' can be slow with very large lists

For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

I like your Worksheet Change idea. One question : the code End(xlUp)(2)
is the same as End(xlUp).Cells(2, 1).Value (I worked out, eventually!).
Is the (2) some shortcut notation?

Cheers,

Mark,



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!