Sort Array Alphabetically

**gsurge** · 10-04-2012, 10:21 AM

Hello,

I am successfully able to sort through a range of cells and allocate names to various arrays based on specified criteria. Those arrays are then used to populate listboxes in a userform.

I am stuck on how to sort the array alphabetically before populating the listbox with the names.

Any help would be great...please see bottom of code below for populating listbox section

'!CREATE NEW ARRAY TO RETRIEVE MANAGER NAMES FOR POSITIONS IN THE ORIGINAL ARRAY THAT MEET CRITERIA (HAVE VALUES)!


RowCounter = 6
ListCounter = 0
Do While Not IsEmpty(Sheets("matrix").Range("A" & RowCounter).Value)

    If CorrelArray4(ListCounter) <> 0 Then
    CorrelArrayADDNAME(ListCounter) = Sheets("matrix").Range("E" & RowCounter).Value
    Else: End If
    
ListCounter = ListCounter + 1
RowCounter = RowCounter + 1
Loop


'!REMOVE BLANKS FROM ARRAYS (MANAGERS THAT DO NOT MEET CRITERIA)


ReDim CorrelArrayADD(LBound(CorrelArrayADDNAME) To UBound(CorrelArrayADDNAME))
For h = LBound(CorrelArrayADDNAME) To UBound(CorrelArrayADDNAME)
    If CorrelArrayADDNAME(h) <> "" Then
        CorrelArrayADD(K) = CorrelArrayADDNAME(h)
    K = K + 1
    End If
Next
ReDim Preserve CorrelArrayADD(LBound(CorrelArrayADDNAME) To K)


'Populate list boxes

With OutputRep.listMANUAL
.Clear
.List = CorrelArrayADD
End With

    OutputRep.Show
    
End Sub

**patel45** · 10-04-2012, 10:37 AM

why not sort on sheet, then put in array ?

**gsurge** · 10-04-2012, 10:45 AM

I figured that was also an option, but I thought it may be easier to do it directly in vb...can you please recommend the code for that process?

**Leith Ross** · 10-04-2012, 11:17 AM

Hello gsurge,

This macro will sort a list in either ascending (A-Z) or descending (Z-A) order. The default is ascending. The list can be either 1-D or 2-D single column.

Function SortArray(ByVal Data As Variant, Optional Descending As Boolean) As Variant

    ' Written: July 05, 2011
    ' Author:  Leith Ross
    ' Summary: Sorts a 1-D Array or 2-D Array in either ascending (default)
    '          or descending order using the Bubble sort algorithm. The function
    '          returns the original 2-D array with the first column sorted or
    '          the 1-D array as a sorted 2-D (UB x 1) array. if Data is not an
    '          array then the reutrn value is set to a boolean False.
  
    Dim arr As Variant
    Dim LB As Long
    Dim I As Long
    Dim J As Long
    Dim UB As Long
    Dim Temp As Variant
  
        Select Case TypeName(Data)
            Case Is = "Range", "Variant()"
                arr = Data
                On Error Resume Next
                    UB = UBound(Data, 2)
                On Error GoTo 0
                If UB > 0 Then
                  ' 2-D Array with multiple columns
                    arr = Data
                Else
                  ' 1-D Array - Convert to a 2-D (UB x 1)
                    arr = WorksheetFunction.Transpose(Data)
                End If
            Case Else
                SortArray = False
        End Select
    
        LB = LBound(arr)
        UB = UBound(arr)
    
            For I = LB To UB
                For J = LB To UB - 1
                    If Descending Xor (arr(I, LB) < arr(J, LB)) Then
                        Temp = arr(J, LB)
                        arr(J, LB) = arr(I, LB)
                        arr(I, LB) = Temp
                    End If
                Next J
            Next I
        
        SortArray = arr
     
End Function

**gsurge** · 10-04-2012, 11:36 AM

Thanks Leith. I placed your code in a module and called it using:

Call SortArray(CorrelArrayADD)

But this didn't sort the array...did I miss a step?

**Kyle123** · 10-04-2012, 11:11 AM

Have a look here http://www.excelforum.com/excel-prog...95#post2954995

**mike7952** · 10-04-2012, 11:19 AM

try this

Dim CorrelArrayADD
    With CreateObject("system.collections.arraylist")
        With Sheets("matrix")
        Do While Not IsEmpty(.Range("A" & RowCounter).Value)
            If Len(Trim(.Range("E" & RowCounter).Value)) > 0 Then
                .Add .Range("E" & RowCounter).Value
            End If
            RowCounter = RowCounter + 1
        Loop
        .Sort
       CorrelArrayADD = .toarray
    End With
    With OutputRep.listMANUAL
        .Clear
        .List = CorrelArrayADD
    End With

**gsurge** · 10-04-2012, 11:40 AM

Nilem,

That worked perfectly! Thank you so much!

One quick thing...I still have a space at the beginning of my array...apparently I didn't remove it. Any idea on how to get rid of this?

**nilem** · 10-04-2012, 11:58 AM

Originally Posted by gsurge

...I still have a space at the beginning of my array...

change this line

ReDim Preserve CorrelArrayADD(LBound(CorrelArrayADDNAME) To k - 1)

**gsurge** · 10-04-2012, 12:03 PM

thank you again. everything works perfectly.

**nilem** · 10-04-2012, 11:29 AM

as an option

....
With OutputRep.listMANUAL
    .Clear
    .List = ShellSort(CorrelArrayADD)
End With
....

Function ShellSort(x)
Dim Limit As Long, Switch As Long, i As Long, j As Long, tmp
j = (UBound(x) - LBound(x) + 1) \ 2
Do While j > 0
    Limit = UBound(x) - j
    Do
        Switch = LBound(x) - 1
        For i = LBound(x) To Limit
            If x(i) > x(i + j) Then
                tmp = x(i)
                x(i) = x(i + j)
                x(i + j) = tmp
                Switch = i
            End If
        Next
        Limit = Switch - j
    Loop While Switch >= LBound(x)
    j = j \ 2
Loop
ShellSort = x
End Function

**Leith Ross** · 10-04-2012, 11:42 AM

Hello gsurge,

The macro is a Function that returns the original array as a sorted array.

Dim Sorted Array as Variant
    SortedArray = SortArray(CorrelArrayADD)

Sort Array Alphabetically

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Re: Sort Array Alphabetically

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions