List Sort - Pulldown Names

[Philcjr]

I know I am close...but what am I missing?

This code is to sort names that are in a row:

[vba]
'=========================================
Private Sub UserNameSorter()
Dim ws As Worksheet
Dim vTempName As Variant, vMemory As Variant
Dim Y As Long, Z As Long, First As Long, Last As Long, LastCol As Long

Set ws = ThisWorkbook.Sheets("Data")
LastCol = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Column

'Populates the array with the values from the row
With ws
vMemory = .Range(.Cells(4, 2), .Cells(4, LastCol))
End With

'Defines the first and last values in the array
First = LBound(vMemory, 2)
Last = UBound(vMemory, 2)

For Y = First To Last
For Z = Y + 1 To Last
'Compares the values and swaps if need be
If vMemory(1, Y) > vMemory(1, Z) Then
vTempName = vMemory(1, Y)
vMemory(1, Z) = vMemory(1, Y)
vMemory(1, Y) = vTempName
End If
Next Z
Next Y

'Populates the pulldown with the sorted values
For Y = First To Last
UsernamePwd.AddItem vMemory(1, Y)
Next Y
End Sub

[/vba]

The end result is that it keeps duplicating names.

Thanks,
Phil

[Bob Phillips]

[vba]
Private Sub UserNameSorter()
Dim ws As Worksheet
Dim vTempName As Variant, vMemory As Variant
Dim Y As Long, Z As Long, First As Long, Last As Long, LastCol As Long

Set ws = ActiveSheet 'ThisWorkbook.Sheets("Data")
LastCol = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Column

'Populates the array with the values from the row
With ws
vMemory = .Range(.Cells(4, 2), .Cells(4, LastCol))
End With

'Defines the first and last values in the array
First = LBound(vMemory, 2)
Last = UBound(vMemory, 2)

For Y = First To Last
For Z = Y + 1 To Last
'Compares the values and swaps if need be
If vMemory(1, Y) > vMemory(1, Z) Then
vTempName = vMemory(1, Y)
vMemory(1, Y) = vMemory(1, Z)
vMemory(1, Z) = vTempName
End If
Next Z
Next Y

'Populates the pulldown with the sorted values
For Y = First To Last
UsernamePwd.AddItem vMemory(1, Y)
Next Y
End Sub
[/vba]

but why not use Excel's sort?

[debauch]

Hmm....that does seem like a lot of code for a bulit-in function?

[Philcjr]

Bob,

As usual, you come through...thanks

To answer the question, "Why not just use the sorting function in Excel?"

On a separate sheet I have names in many columns with respective data listed below.

These names are added sporadically to the file, so I figured it would be easier to sort the names before putting them in the pull-down... I guess I could have just coded the range to sort itself... always many ways to skin the cat.

Thanks again,
Phil

[Bob Phillips]

Bob,

As usual, you come through...thanks

To answer the question, "Why not just use the sorting function in Excel?"

On a separate sheet I have names in many columns with respective data listed below.

These names are added sporadically to the file, so I figured it would be easier to sort the names before putting them in the pull-down... I guess I could have just coded the range to sort itself... always many ways to skin the cat.

Thanks again,
Phil

Phil,

I didn't mean sort within Excel, but use Excel's built-in sort, even if doen within VBA. That can then easily cater for the variable data.

[Philcjr]

Bob,

I see what you were talking about. I was actually playing with different sorts and wanted to learn how to do the sort through arrays... I guess I just got caught up in trying to make it work. Looking back, your right, I using Excel's native funtionality is a better way... no need to re-create the wheel. Next time I will try coding for the built-in sort. For now all is working and working well.