Sorting a listbox via code Update...not working with columns with null/blanks

excel vba 2010.

Using this code for a long time(from the web).  WORK GREAT.

Unless the column you are sorting in has blank/nulls

Any help on getting around this problem.


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

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

                '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(vaItems(i, sCol)) > CInt(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 CInt(vaItems(i, sCol)) < CInt(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
    End If
    
    'Set the list to the array
    oLb.List = vaItems
    
''Sort by the 1st column in the ListBox Alphabetically in Ascending Order
'Run "SortListBox", ListBox1, 0, 1, 1

''Sort by the 1st column in the ListBox Alphabetically in Descending Order
'Run "SortListBox", ListBox1, 0, 1, 2

''Sort by the 2nd column in the ListBox Numerically in Ascending Order
'Run "SortListBox", ListBox1, 1, 2, 1

''Sort by the 2nd column in the ListBox Numerically in Descending Order
'Run "SortListBox", ListBox1, 1, 2, 2

Open in new window



Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
How do you want the blanks treated?
0
FordraidersAuthor Commented:
either way, asc or desc  to the bottom of the sort.
if possible
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

I have included the following update to the SortListBox() subroutine within the attached workbook:

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

    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 vaItems(i, sCol) > vaItems(j, sCol) 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(vaItems(i, sCol)) > CInt(vaItems(j, sCol)) 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 = vaItems(i, c)
'                           vaItems(i, c) = vaItems(j, c)
                            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(vaItems(i, sCol)) < CInt(vaItems(j, sCol)) 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 = vaItems(i, c)
'                           vaItems(i, c) = vaItems(j, c)
                            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
  
SortListBox_Error:

  MsgBox "ERROR #" & CStr(Err.Number) & _
         Err.Description, _
         vbExclamation Or vbOKOnly, _
         ThisWorkbook.Name
  
  Resume Next
  
End Sub

Open in new window



Hopefully this should address  your requirements.

Please note that if you change the use of CInt() to accommodate a different numeric data type, for example, CDbl(), then the two uses of 32767 within the updated code will also need to change to the largest value the data type can support (1.79769313486231E308 for Double).

The attached workbook also includes a Form with a ListBox to demonstrate the changes I have made.

BFN,

fp.
Q-28235471.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
ABSOLUTELY PERFECT !!!!   Thanks Veryy very much !
0
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Good luck with the rest of your project.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.