My solution to looking up a column and make a validate list...

[Dewi...]

I wanted to look up a column with hundreds of names (Employers) and
wanted to get one of each name put it in a list so I can make a
validation list for a form with the names. It also sorts the list
alphabeticaly.

Any questions please ask ;-)


Sheets("Employer").Select
Sheets("Details").Range("f1:f5000").AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=Range("A1:A100"), Unique:=True

' sorts in order
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Put something like this to to take it to where you would like to end
up after the macro has run

Sheets("Form").Select
Range("A3").Select

something back

 

[Don Guillett]

Try this idea from anywhere in the workbook with NO selections.
Sub filteruniqueandcopy()
With Sheets("sheet5")'source sheet
.Range("a2:a50").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.Range("a2:a50").Copy Sheets("yyy").Range("f1")
.ShowAllData
End With
With Sheets("yyy")
.Columns("a").Sort Key1:=.Range("a1"), Order1:=xlAscending
End With
End Sub