Category: [Excel]  Demo Available 

---

Sort an Array

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

Sort an Array.  

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

Index to Excel VBA Code

Sort by Color In Excel

Sort Alphanumeric Text

Sort Excel Worksheets/Sheets in Excel

 

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

---

• 
• 
• 
• 
• 
•