You can use the code from John Walkenbach's site posted by Nigel to get a
unique list, or you can use code from Ron de Bruin's site which includes code
to do the copying as well.

http://www.rondebruin.nl/copy5.htm

--
Regards,
Tom Ogilvy


"Nigel" wrote:

The drop down list in auto-filter is an internal Excel function which you
cannot access other than through the user-interface. You could try using
the advanced filter and copy the result to another location removing
duplicates by selecting unique records only, then sorting the list ? Or in
VBA you create a new collection and sort that, collections do not allow
duplicate keys. Example follows...where a collection called 'NoDupes' is
created, and sorted

Sub RemoveDuplicates()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items list are in A2:A106
Set AllCells = Range("A2:A106")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!

On Error Resume Next
For Each Cell In AllCells
If Not Cell.EntireRow.Hidden Then
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
End If
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
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
End Sub

--
Cheers
Nigel



"JeffMelton" wrote in message
ups.com...
If you run an autofilter in column A and the drop down shows:
Adam
Bob
Dave
George
Mark
Steven
etc

Is there a way to reference Adam as 1 or something? Bob would be 2 Dave
would be 3 etc.

What I'm thinking is something like autofilter A and then criteria
would be Adam so I can copy and paste to a new sheet. Right now I have
maybe 800 different names and I'm trying to figure out an easier way to
move each individual entry and the number will only go up.