sort items of listboxes with multiple columns
 

Hi,

I have 2 questions that are related.

I would like to sort the items in a listbox that contains 3 columns. What
is the code to sort by the first column, 2nd column, and 3rd column? Or do I
have to sort the data first before I put it into the columns of the listbox?

Secondly, is there a way to sort the values in an array after the values
have been stored in the array?

Thank you for the help!

sort items of listboxes with multiple columns
 

There is no built in support for sorting data in listboxes, so you would
have to either sort it before putting it in, sort it as you put it in, or
sort it in the list.

Here is some code to sort an array. .

The second procedure shows how to call it.

Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'Modified to sort on single column
'Modified to do Ascending or Descending
Dim i, j, X, Y, mm

i = L
j = R
X = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) X And i < R)
i = i + 1
Wend
While (X SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
End Sub




Sub aaTesterSort()
Dim bAscending As Boolean
Set rng = Range("I7").CurrentRegion
vArr = rng.Value
bAscending = False
QuickSort vArr, 5, LBound(vArr, 1), UBound(vArr, 1), bAscending
Range("I26").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value = vArr
End Sub

--
Regards,
Tom Ogilvy

"borg" wrote in message
...
Hi,

I have 2 questions that are related.

I would like to sort the items in a listbox that contains 3 columns. What
is the code to sort by the first column, 2nd column, and 3rd column? Or
do I
have to sort the data first before I put it into the columns of the
listbox?

Secondly, is there a way to sort the values in an array after the values
have been stored in the array?

Thank you for the help!

sort items of listboxes with multiple columns
 

Thank you very much!

"Tom Ogilvy" wrote:

There is no built in support for sorting data in listboxes, so you would
have to either sort it before putting it in, sort it as you put it in, or
sort it in the list.

Here is some code to sort an array. .

The second procedure shows how to call it.

Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'Modified to sort on single column
'Modified to do Ascending or Descending
Dim i, j, X, Y, mm

i = L
j = R
X = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) X And i < R)
i = i + 1
Wend
While (X SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
End Sub




Sub aaTesterSort()
Dim bAscending As Boolean
Set rng = Range("I7").CurrentRegion
vArr = rng.Value
bAscending = False
QuickSort vArr, 5, LBound(vArr, 1), UBound(vArr, 1), bAscending
Range("I26").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value = vArr
End Sub

--
Regards,
Tom Ogilvy

"borg" wrote in message
...
Hi,

I have 2 questions that are related.

I would like to sort the items in a listbox that contains 3 columns. What
is the code to sort by the first column, 2nd column, and 3rd column? Or
do I
have to sort the data first before I put it into the columns of the
listbox?

Secondly, is there a way to sort the values in an array after the values
have been stored in the array?

Thank you for the help!