VBA list sort function help/guidance

[russell84]

Hi vba gurus

 

How do you sort a list in vba so that the list is a-z no matter of capitals or lower case EG A-a-B-b-C-c-D-d etc

 

Also can you sort the list so that if it is a number it is at the start of the list

 

eg 0,1,2,3,4,5,6,7,8,9,A,a,B,b,C,c,D,d,E,e,........Z,z

 

Thanks guys:D

[rkmcswain]

Review the Option Compare Text statement.

 

http://msdn.microsoft.com/en-us/library/8t3khw5f(VS.80).aspx

 

http://www.ozgrid.com/VBA/vba-case-sensitive.htm

[russell84]

Cheers - i'll read now.

 

Thanks

[russell84]

I found one already done posted on a different site - i thought it might be useful to others so i'll post it here

 

The below code will sort a list box - wether it has 1,2,3 etc columns

 

[color=#0000ff]Sub[/color] SortListBox(oLb [color=#0000ff]As[/color] MSForms.ListBox, sCol [color=#0000ff]As[/color] [color=#0000ff]Integer[/color], sType [color=#0000ff]As[/color] [color=#0000ff]Integer[/color], sDir [color=#0000ff]As[/color] [color=#0000ff]Integer[/color]) 
   [color=#0000ff]Dim[/color] vaItems [color=#0000ff]As[/color] [color=#0000ff]Variant[/color] 
   [color=#0000ff]Dim[/color] i [color=#0000ff]As[/color] [color=#0000ff]Long[/color], j [color=#0000ff]As[/color] [color=#0000ff]Long[/color] 
   [color=#0000ff]Dim[/color] c [color=#0000ff]As[/color] [color=#0000ff]Integer[/color] 
   [color=#0000ff]Dim[/color] vTemp [color=#0000ff]As[/color] [color=#0000ff]Variant[/color] 
    
    [color=#006400]'Put the items in a variant array[/color]
   vaItems = oLb.List 
    
    [color=#006400]'Sort the Array Alphabetically(1)[/color]
   [color=#0000ff]If[/color] sType = 1 [color=#0000ff]Then[/color] 
       [color=#0000ff]For[/color] i = [color=#0000ff]LBound[/color](vaItems, 1) [color=#0000ff]To[/color] [color=#0000ff]UBound[/color](vaItems, 1) - 1 
           [color=#0000ff]For[/color] j = i + 1 [color=#0000ff]To[/color] [color=#0000ff]UBound[/color](vaItems, 1) 
                [color=#006400]'Sort Ascending (1)[/color]
               [color=#0000ff]If[/color] sDir = 1 [color=#0000ff]Then[/color] 
                   [color=#0000ff]If[/color] vaItems(i, sCol) > vaItems(j, sCol) [color=#0000ff]Then[/color] 
                       [color=#0000ff]For[/color] c = 0 [color=#0000ff]To[/color] oLb.ColumnCount - 1 [color=#006400]'Allows sorting of multi-column ListBoxes[/color]
                           vTemp = vaItems(i, c) 
                           vaItems(i, c) = vaItems(j, c) 
                           vaItems(j, c) = vTemp 
                       [color=#0000ff]Next[/color] c 
                   [color=#0000ff]End[/color] [color=#0000ff]If[/color] 
                    
                    [color=#006400]'Sort Descending (2)[/color]
               [color=#0000ff]ElseIf[/color] sDir = 2 [color=#0000ff]Then[/color] 
                   [color=#0000ff]If[/color] vaItems(i, sCol) < vaItems(j, sCol) [color=#0000ff]Then[/color] 
                       [color=#0000ff]For[/color] c = 0 [color=#0000ff]To[/color] oLb.ColumnCount - 1 [color=#006400]'Allows sorting of multi-column ListBoxes[/color]
                           vTemp = vaItems(i, c) 
                           vaItems(i, c) = vaItems(j, c) 
                           vaItems(j, c) = vTemp 
                       [color=#0000ff]Next[/color] c 
                   [color=#0000ff]End[/color] [color=#0000ff]If[/color] 
               [color=#0000ff]End[/color] [color=#0000ff]If[/color] 
                
           [color=#0000ff]Next[/color] j 
       [color=#0000ff]Next[/color] i 
        [color=#006400]'Sort the Array Numerically(2)[/color]
        [color=#006400]'(Substitute CInt with another conversion type (CLng, CDec, etc.) depending on type of numbers in the column)[/color]
   [color=#0000ff]ElseIf[/color] sType = 2 [color=#0000ff]Then[/color] 
       [color=#0000ff]For[/color] i = [color=#0000ff]LBound[/color](vaItems, 1) [color=#0000ff]To[/color] [color=#0000ff]UBound[/color](vaItems, 1) - 1 
           [color=#0000ff]For[/color] j = i + 1 [color=#0000ff]To[/color] [color=#0000ff]UBound[/color](vaItems, 1) 
                [color=#006400]'Sort Ascending (1)[/color]
               [color=#0000ff]If[/color] sDir = 1 [color=#0000ff]Then[/color] 
                   [color=#0000ff]If[/color] [color=#0000ff]CInt[/color](vaItems(i, sCol)) > [color=#0000ff]CInt[/color](vaItems(j, sCol)) [color=#0000ff]Then[/color] 
                       [color=#0000ff]For[/color] c = 0 [color=#0000ff]To[/color] oLb.ColumnCount - 1 [color=#006400]'Allows sorting of multi-column ListBoxes[/color]
                           vTemp = vaItems(i, c) 
                           vaItems(i, c) = vaItems(j, c) 
                           vaItems(j, c) = vTemp 
                       [color=#0000ff]Next[/color] c 
                   [color=#0000ff]End[/color] [color=#0000ff]If[/color] 
                    
                    [color=#006400]'Sort Descending (2)[/color]
               [color=#0000ff]ElseIf[/color] sDir = 2 [color=#0000ff]Then[/color] 
                   [color=#0000ff]If[/color] [color=#0000ff]CInt[/color](vaItems(i, sCol)) < [color=#0000ff]CInt[/color](vaItems(j, sCol)) [color=#0000ff]Then[/color] 
                       [color=#0000ff]For[/color] c = 0 [color=#0000ff]To[/color] oLb.ColumnCount - 1 [color=#006400]'Allows sorting of multi-column ListBoxes[/color]
                           vTemp = vaItems(i, c) 
                           vaItems(i, c) = vaItems(j, c) 
                           vaItems(j, c) = vTemp 
                       [color=#0000ff]Next[/color] c 
                   [color=#0000ff]End[/color] [color=#0000ff]If[/color] 
               [color=#0000ff]End[/color] [color=#0000ff]If[/color] 
                
           [color=#0000ff]Next[/color] j 
       [color=#0000ff]Next[/color] i 
   [color=#0000ff]End[/color] [color=#0000ff]If[/color] 
    
    [color=#006400]'Set the list to the array[/color]
   oLb.List = vaItems 
[color=#0000ff]End Sub[/color] 

 

You can call the code by using the below.

To sort listbox1 for example just call the below in a sub

 

 'Sort by the 1st column in the ListBox Alphabetically in Ascending Order
SortListBox ListBox1, 0, 1, 1
'Sort by the 1st column in the ListBox Alphabetically in Descending Order
SortListBox ListBox1, 0, 1, 2
'Sort by the 2nd column in the ListBox Numerically in Ascending Order
SortListBox ListBox1, 1, 2, 1
'Sort by the 2nd column in the ListBox Numerically in Descending Order
SortListBox ListBox1, 1, 2, 2