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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dewi... - 17 Jun 2007 22:39 GMT

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

Reply to this Message

Don Guillett - 18 Jun 2007 14:06 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>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
[quoted text clipped - 23 lines]
>
> something back

Reply to this Message

Rate this thread: