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

  • Thread starter Thread starter Dewi...
  • Start date Start date
D

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
 
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
 
Back
Top