Sorting a listbox with price and moving 0.00 to bottom of list

Excel 2010 vba

I have some code that I currently use to sort a listbox by a price column.
Call line:
Run "SortListBox", UserForm2.ListBox32, 11, 1, 1
' this is sorting column 11 , 1 as integer, 1 as ascending by price.

What I need:  I need to keep any prices that have a 0.00 price at the bottom of the sort.

Example:
column1      ......    column 11
4r565                      0.00
2a223                     2.33
3e432                     5.56
3se127                    1.34
3e4877                   11.56
5r4345                    0.00

After code fix:
column1      ......    column 11
3se127                   1.34
2a223                     2.33
3e432                     5.56
3e4877                   11.56
5r4345                    0.00
4r565                      0.00

code below:
Public Sub SortListBox(oLb As MSForms.ListBox, sCol As Integer, sType As Integer, sDir As Integer)

Dim vaItems As Variant
Dim i As Long, j As Long
Dim C As Integer
Dim vTemp As Variant

'Put the items in a variant array
' On Error GoTo SortListBox_Error
On Error Resume Next
vaItems = oLb.List

'Sort the Array Alphabetically(1)
If sType = 1 Then
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
'Sort Ascending (1)
If sDir = 1 Then
If IIf(Len(Trim\$(IIf(IsNull(vaItems(i, sCol)), "", vaItems(i, sCol)))) = 0, String\$(255, Chr\$(255)), vaItems(i, sCol)) > _
IIf(Len(Trim\$(IIf(IsNull(vaItems(j, sCol)), "", vaItems(j, sCol)))) = 0, String\$(255, Chr\$(255)), vaItems(j, sCol)) Then
For C = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, C)
vaItems(i, C) = vaItems(j, C)
vaItems(j, C) = vTemp
Next C
End If

'Sort Descending (2)
ElseIf sDir = 2 Then
If vaItems(i, sCol) < vaItems(j, sCol) Then
For C = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = vaItems(i, C)
vaItems(i, C) = vaItems(j, C)
vaItems(j, C) = vTemp
Next C
End If
End If

Next j
Next i
'Sort the Array Numerically(2)
'(Substitute CInt with another conversion type (CLng, CDec, etc.) depending on type of numbers in the column)
ElseIf sType = 2 Then
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
'Sort Ascending (1)
If sDir = 1 Then
If CInt(IIf(Len(Trim\$(IIf(IsNull(vaItems(i, sCol)), "", vaItems(i, sCol)))) = 0, 32767, vaItems(i, sCol))) > _
CInt(IIf(Len(Trim\$(IIf(IsNull(vaItems(j, sCol)), "", vaItems(j, sCol)))) = 0, 32767, vaItems(j, sCol))) Then
For C = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
vTemp = IIf(Len(Trim\$(IIf(IsNull(vaItems(i, C)), "", vaItems(i, C)))) = 0, "", vaItems(i, C))
vaItems(i, C) = IIf(Len(Trim\$(IIf(IsNull(vaItems(j, C)), "", vaItems(j, C)))) = 0, "", vaItems(j, C))
vaItems(j, C) = vTemp
Next C
End If

'Sort Descending (2)
ElseIf sDir = 2 Then
If CInt(IIf(Len(Trim\$(IIf(IsNull(vaItems(i, sCol)), "", vaItems(i, sCol)))) = 0, "0", vaItems(i, sCol))) < _
CInt(IIf(Len(Trim\$(IIf(IsNull(vaItems(j, sCol)), "", vaItems(j, sCol)))) = 0, "0", vaItems(j, sCol))) Then
For C = 0 To oLb.ColumnCount - 1 'Allows sorting of multi-column ListBoxes

vTemp = IIf(Len(Trim\$(IIf(IsNull(vaItems(i, C)), "", vaItems(i, C)))) = 0, "", vaItems(i, C))
vaItems(i, C) = IIf(Len(Trim\$(IIf(IsNull(vaItems(j, C)), "", vaItems(j, C)))) = 0, "", vaItems(j, C))
vaItems(j, C) = vTemp
Next C
End If
End If

Next j
Next i
End If

'Set the list to the array
oLb.List = vaItems

Exit Sub

thanks
fordraiders
Martin Liss

How about adding a column with a width of zero, placing 99999 in that column when column 11 is zero and then sorting on the new column instead on column 11?

ok, i'm using getrows to fill the listbox.

and .columnwidths to make the litbox appear the way i want it to.

Right click on the control, select Properties, and change 'Column Count'.
Martin Liss

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.