Sorting stuff into alphabetical order

[Michelle]

I've got a list of names that I want to put into a drop-down - in
Alphabetical order...

I know, I could put them into a sheet, sort them then add them into the
drop-down, but...

Is there a clever way that I can get them in order without dumping them all
out, sorting them and bringing them back in?

Thanks

M

 

[Steve Yandl]

Michelle,

I don't know if what I have below is more efficient than moving the list to
a sheet and making Excel perform the sort. I used the original version in a
vbScript within an HTA where I didn't have Excel to rely on. In the example
below, the original list is in A1:A10 on the active sheet and it isn't
alphabetized. The list is retrieved, alphabetized and used to populate
combobox1 in a user form.

'-----------------------------------------
Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'-----------------------------------------

Steve Yandl

 

[Bernd P]

I've got a list of names that I want to put into a drop-down - in
Alphabetical order...

I know, I could put them into a sheet, sort them then add them into the
drop-down, but...

Is there a clever way that I can get them in order without dumping them all
out, sorting them and bringing them back in?

Thanks

M

Hello Michelle,

Sorting with worksheet functions:
http://sulprobil.com/html/sorting.html

Regards,
Bernd

 

[Jim Cone]

Where, what ,how is the list of names?
Are they in an Array? How many dimensions?
Are they in a collection?
Are they in a Dictionary?
Are they in something else?
Why do you want to avoid the Excel worksheet sort? It is one of the fastest available.
How many names approximately are there? 20, 2000, 200000?
What operating system and Excel version are you using?
--
Jim Cone
Portland, Oregon USA
(Special Sort... http://www.contextures.com/excel-sort-addin.html )



"Michelle" <[email protected]>
wrote in message
I've got a list of names that I want to put into a drop-down - in
Alphabetical order...
I know, I could put them into a sheet, sort them then add them into the
drop-down, but...
Is there a clever way that I can get them in order without dumping them all
out, sorting them and bringing them back in?
Thanks
M

 

[Michelle]

It does exactly what I want!

M

Michelle,

I don't know if what I have below is more efficient than moving the list
to a sheet and making Excel perform the sort. I used the original version
in a vbScript within an HTA where I didn't have Excel to rely on. In the
example below, the original list is in A1:A10 on the active sheet and it
isn't alphabetized. The list is retrieved, alphabetized and used to
populate combobox1 in a user form.

'-----------------------------------------
Private Sub UserForm_Activate()

Const adVarChar = 200
Const MaxCharacters = 255

Dim R As Integer

Set DataList = CreateObject("ADOR.Recordset")
DataList.Fields.Append "MyList", adVarChar, MaxCharacters
DataList.Open

For R = 1 To 10
DataList.AddNew
DataList("MyList") = Cells(R, 1).Value
DataList.Update
Next R

DataList.Sort = "MyList"

DataList.MoveFirst
Do Until DataList.EOF
ComboBox1.AddItem DataList.Fields.Item("MyList")
DataList.MoveNext
Loop

Set DataList = Nothing

End Sub

'-----------------------------------------

Steve Yandl

 

[Mike]

Where are you getting the names from??