Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

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
Avatar of Norie
Norie

How do you want the blanks treated?
Avatar of Fordraiders

ASKER

either way, asc or desc  to the bottom of the sort.
if possible
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ABSOLUTELY PERFECT !!!!   Thanks Veryy very much !
You're very welcome.

Good luck with the rest of your project.