Avatar of Fordraiders
Fordraiders
Flag for United States of America asked on

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
Microsoft Excel

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
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?
Fordraiders

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

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

how to ad additional column ?
Martin Liss

Right click on the control, select Properties, and change 'Column Count'.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Fordraiders

ASKER
Thanks
Martin Liss

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014