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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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 |
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
Great! Thanks!
How can I sort data by column two.
@Ahmed: Change the comparison line to look at the second element of the array instead of the first
Everything else stays the same.
Hello,
Thanks for your bubble sort code. It has been a great help as I am a newbie to VBA code.
I have used your code to populate a list box with 5 columns. The first column (col 0) in the listbox is numerical with numbers between 1 to 40. Unfortunately, the sort on numbers produces something like 1,10,11, 12, 2, 21, 22 etc. Is there anyway I could change your code so that the code could sort the numbers correctly i.e 1,2,3,4 etc.
I do not know very much about VBA code, so any help you could give would be greatly appreciated.
Thank you
how do populate 2 columns that are independent values. ie you can select fish in the second column only. When I populate it puts scroll bar on the list box and does not fill the second column
‘Same code for 3 columns including reverse sorting :
‘My columns are File Name, File Type and File Path, so the names of the buttons
Marmouille’s code took it a step further and I love how it accommodates multiple columns. My need is for 8+ columns, which would have made this code very long, so I went even further with an additional loop for the column count. I set up a combobox that populates the column names and based on the listindex, it will sort by that column.
HOWEVER, I’ve found that the sorting power is limited to the first character, only, both for numbers AND strings.
so, alphabetically, I see it sort string fields in a way that I see:
AAA, Copy, Duplicate, Orbital, TRC, This. The last two, should have been reversed. “This” should come before “TRC”, correct?
In another field, I have Dollar values. It starts with $0.00. Great. I then see $1,224.65, then I see $100.42, $112.27, $190, $193, $528, etc. I think the “,” caused the $1,224 to show up BEFORE the $100. Not good. How do I get around that?
For dates: 12/14 comes BEFORE 12/8. Maybe that wouldn’t be the case if it was 12/”08″, but it’s not, so what to do?
Here’s the code for anyone else using listboxes with many columns:
Christopher: There are two types of comparisons that VBA can do: Binary and Text. Binary is the default. That means that
“TRC” < “This”
because a capital R has a lower ascii value than a lower case h. If you want to change the default you put
Option Compare Textat the top of your module and almost all comparisons will be Text. I never do this. If I need a text compare, I useStrComp.A return value of 1 means the first string is greater than the second. See https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/strcomp-function for all the return values.
As for numbers, everything in a ListBox is a string – even if it looks like a number. To compare them as numbers, you have to convert them. You could do something like this
Dick, that is Brilliant! Thank you!
last thing… I have two columns that are date fields: (Created / Modified), which are populated via VBA with c.value = Now
I notice with your code, everything else works great for both Text AND number fields, but the dates are still wonky. They are formatted like so:
12/8/17 4:32 PM
in the format dialog box, these columns are Category: Date, Type: 3/14/12 1:30 PM
The sort function for these fields has 1/15 showing up sooner than 1/3. 1/6 comes before 12/1. Any ideas on how I can sort them via timestamp?
Never Mind, I got it working by nesting another if statement to handle the listbox columns differently, based on whether the combobox indicated to sort via “Created” or “Modified” (Both of which are date/time fields). If so, then I used the CDate() method to test the strings as dates, which worked wonderfully. Here’s the final code:
Christopher, how is your final code excuated?
Hi Would the sort code work if I wanted to sort on date order?
Andrew, see Christopher Smith’s code two comments up. He uses CDATE() to convert the Listbox entry to a date while comparing them.