Sort an Array

NEW! More Books..

Add Excel Answers & Search To Your Google Toolbar Details

Excel VBA: Sort/Sorting an Array of Text. Sort a 1 Dimensional Array

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download

Sort an Array. See Also: Sort Sheets/Worksheets

The Excel VBA macro code below can be used in any Excel Workbook. When run, it will sort the 1 dimensional array of strings, MyArray. It will output the unsorted array of values to A1:A10, then sort the array, then output the new sorted array of values to B1:B10. It should be noted that one could also output the array to an Excel Worksheet, sort using Excel's built in sort, then fill the array again with the sorted values. In this example, we use an array of text strings, but it could be an array of numeric values.

In the Excel macro code below outputs the array to a one column range with the help of the Transpose Worksheet Function. We could also output it to a one row range (without Transpose) like shown below;

Range(Cells(1, 1), Cells(1, UBound(MyArray))) = MyArray

To use the code, go to Tools>Macro>Visual Basic Editor (Alt+F11) and then to Insert>Module and paste in the code below.

Sub SortArray()
Dim MyArray(10) As String
Dim lLoop As Long, lLoop2 As Long
Dim str1 As String
Dim str2 As String

    'Fill array
    For lLoop = 0 To 9
        If lLoop = 0 Then
          MyArray(lLoop) = "Zoo"
        Else
            MyArray(lLoop) = Choose(lLoop, "Farm", "Paddock", "Sheep", _
                        "Cow", "Bird", "Mice", "Chicken", "Fence", "Post", "Lamb")
        End If
    Next lLoop
    
    'Output unsorted array
    Range("A1:A" & UBound(MyArray) + 1) = _
    WorksheetFunction.Transpose(MyArray)

    
    'Sort array
    For lLoop = 0 To UBound(MyArray)
       For lLoop2 = lLoop To UBound(MyArray)
            If UCase(MyArray(lLoop2)) < UCase(MyArray(lLoop)) Then
                str1 = MyArray(lLoop)
                str2 = MyArray(lLoop2)
                MyArray(lLoop) = str2
                MyArray(lLoop2) = str1
            End If
        Next lLoop2
    Next lLoop
    
    'Output sorted array
    Range("B1:B" & UBound(MyArray) + 1) _
    = WorksheetFunction.Transpose(MyArray)

End Sub

See Also: Sort Sheets/Worksheets

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft