Sorting a MultiColumn ListBox
This example sorts the items in a two-column ListBox. Here’s what the unsorted example ListBox looks like
The code behind the Sort button looks like this
Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
Dim sTemp As String
Dim sTemp2 As String
Dim LbList As Variant
'Store the list in an array for sorting
LbList = Me.ListBox1.List
'Bubble sort the array on the first value
For i = LBound(LbList, 1) To UBound(LbList, 1) - 1
For j = i + 1 To UBound(LbList, 1)
If LbList(i, 0) > LbList(j, 0) Then
'Swap the first value
sTemp = LbList(i, 0)
LbList(i, 0) = LbList(j, 0)
LbList(j, 0) = sTemp
'Swap the second value
sTemp2 = LbList(i, 1)
LbList(i, 1) = LbList(j, 1)
LbList(j, 1) = sTemp2
End If
Next j
Next i
'Remove the contents of the listbox
Me.ListBox1.Clear
'Repopulate with the sorted list
Me.ListBox1.List = LbList
End Sub
Dim i As Long
Dim j As Long
Dim sTemp As String
Dim sTemp2 As String
Dim LbList As Variant
'Store the list in an array for sorting
LbList = Me.ListBox1.List
'Bubble sort the array on the first value
For i = LBound(LbList, 1) To UBound(LbList, 1) - 1
For j = i + 1 To UBound(LbList, 1)
If LbList(i, 0) > LbList(j, 0) Then
'Swap the first value
sTemp = LbList(i, 0)
LbList(i, 0) = LbList(j, 0)
LbList(j, 0) = sTemp
'Swap the second value
sTemp2 = LbList(i, 1)
LbList(i, 1) = LbList(j, 1)
LbList(j, 1) = sTemp2
End If
Next j
Next i
'Remove the contents of the listbox
Me.ListBox1.Clear
'Repopulate with the sorted list
Me.ListBox1.List = LbList
End Sub
And the sorted ListBox
Hi,
I was wondering if you could help with a simple problem. I am not very used to VBA functions. I was wondering if you could help with a simple VBA routine that allows me to sort the data in a column in descending order. I have 3 columns with data and want to sort the column 1 in descending order with corresponding data in column 2 and 3 matching the corresponding column 1 data.
I am able to record macros and do it, but I would need a function that actually creates a user defined function in the dropdown function list in the spreadhsheet. Putting the recorded macros in a function did not help. I guess its very simple for expert user. I would need a user defined function for my problem to be solved.
Would appreciate your help.
Thanks a lot
Sudeep
Bird, Reptile, Fish, Mammal is alphabetic? I could have sworn R would come after F and M!
hello , can i use those lists also in vb.net ?
i couldn’t find multicolumn-lists there
regards
raimund
No andy, “Finch, Gilla Monster, Shark, Zebra” is alphabetic…
How can one still use the bubble sort? The worst sorting algorithm invented by man!
The much maligned bubble sort is unsophisticated, rough and crude.
It is also often times much easier to implement in certain situations.
I saw comparisons of algorithms for data sets where the quick sort was
1000 times faster. To the modern microprocessor the difference often is
1/10000 of a second verses 1/10th of a second ! (oh how impatient we have become
I appreciate a good kludge but i am unappreciative of “sophisticated”
algorithms that simply do not work. Remember the mantra of programming
that it is 10 times more difficult to debug than it is to write a program.
So not only is simpler often the better route, a program can become so complex that
it transcends the ability of the writer to debug. So lets not just yet throw the bubble
sort under the bus
Gil