Excel VBA Sort Array Alphabetically

Sorting is an essential operation that involves arranging a set of data in ascending or descending order based on their numeric values or leading alphabets. It is very efficient while searching as well as data analysis and data manipulation. Excel can be a useful tool to sort data based on various criteria. In this article, we will show you how to use Excel VBA to sort Array alphabetically.

Download Practice Workbook

You can download and practice this workbook.

Excel VBA Sort Array Alphabetically: 5 Examples

There are several ways to sort an array alphabetically using Excel VBA. This article will guide you to 5 different ways to sort arrays.

This is the general dataset we are using to demonstrate every method. The dataset consists of only book names so that it represents a one-dimensional array. With each code, we are going to take this data from these cells to create the array.

1. Sort Array Alphabetically with For and If Loop

With For and If loops we are going to sort this dataset alphabetically using array in Excel VBA. Both loops are control flow statements. The For loop allows us to repeat a set of actions a specific number of times. The If loop allows a block of code to run only if a specific condition is true.

For this procedure, you can use the following code. Copy the code in a new module and click on the Run button.

VBA Code:

Sub Sort_Array_Alphabetically_with_For_IF()
Dim i As Long, j As Long, k As Long
Dim Book_Array(5 To 18) As Variant
Dim temp As Variant
For i = 5 To 18
Book_Array(i) = Range("B" & i).Value
Next i
For i = LBound(Book_Array) To UBound(Book_Array) - 1
For j = i + 1 To UBound(Book_Array)
If Book_Array(i) > Book_Array(j) Then
temp = Book_Array(j)
Book_Array(j) = Book_Array(i)
Book_Array(i) = temp
End If
Next j
Next i
Range("B5:B18") = Application.Transpose(Book_Array)
End Sub

Dim i As Long, j As Long, k As Long
Dim Book_Array(5 To 18) As Variant
Dim temp As Variant

For i = 5 To 18
Book_Array(i) = Range("B" & i).Value
Next i

For i = LBound(Book_Array) To UBound(Book_Array) - 1
For j = i + 1 To UBound(Book_Array)

If Book_Array(i) > Book_Array(j) Then
temp = Book_Array(j)
Book_Array(j) = Book_Array(i)
Book_Array(i) = temp

End If
Next j
Next i

Range("B5:B18") = Application.Transpose(Book_Array)

After running the code, the dataset will be sorted alphabetically as shown in the image below.

2. Utilising While Loop to Sort Array Alphabetically

We can also use the While loop to sort an array alphabetically. For this procedure, we are going to use the same dataset of Book Names as previously shown. Similar to the For loop, the While loop also runs a block of code multiple times. However, instead of incrementing a step to count the counter, the While loop goes on to run until a specific condition remains true. We will sort the Book Names alphabetically with this loop now.

Copy the following code in a new module and Click on the Run button.

VBA Code with While Loop:

Sub Sort_Array_While_Loop()
    Dim Book_Array(5 To 18) As Variant
    Dim temp As Variant
    Dim i As Long, j As Long
    For k = 5 To 18
        Book_Array(k) = Range("B" & k).Value
    Next k
    i = LBound(Book_Array)
    While i <= UBound(Book_Array)
        j = i + 1
        While j <= UBound(Book_Array)
            If Book_Array(i) > Book_Array(j) Then
                temp = Book_Array(j)
                Book_Array(j) = Book_Array(i)
                Book_Array(i) = temp
            End If
            j = j + 1
        Wend
        i = i + 1
    Wend
    Range("B5:B18") = Application.Transpose(Book_Array)
End Sub

  Dim Book_Array(5 To 18) As Variant
    Dim temp As Variant
    Dim i As Long, j As Long

   For k = 5 To 18
        Book_Array(k) = Range("B" & k).Value
    Next k

    i = LBound(Book_Array)
    While i <= UBound(Book_Array)
        j = i + 1
        While j <= UBound(Book_Array)

          If Book_Array(i) > Book_Array(j) Then
                temp = Book_Array(j)
                Book_Array(j) = Book_Array(i)
                Book_Array(i) = temp

End If
            j = j + 1
        Wend
        i = i + 1
    Wend

    Range("B5:B18") = Application.Transpose(Book_Array)

And, after running the code, you can see the dataset will be sorted alphabetically as shown in the image below.

3. Using Do Until Loop to Sort Array Alphabetically

Do Until Loop continues to execute a certain program until a condition is TRUE. We will use this loop to sort Array alphabetically.

Again, we are using the same dataset as previously worked with.

Copy the following code in a new Module and Click on the Run button.

VBA Code with Do Until:

Sub Sort_Array_Do_Until()
    Dim Book_Array(5 To 18) As Variant
    Dim temp As Variant
    Dim i As Long, j As Long
    For k = 5 To 18
        Book_Array(k) = Range("B" & k).Value
    Next k
    i = LBound(Book_Array)
    Do Until i > UBound(Book_Array)
        j = i + 1
        Do Until j > UBound(Book_Array)
            If Book_Array(i) > Book_Array(j) Then
                temp = Book_Array(j)
                Book_Array(j) = Book_Array(i)
                Book_Array(i) = temp
            End If
            j = j + 1
        Loop
        i = i + 1
    Loop
    Range("B5:B18") = Application.Transpose(Book_Array)
End Sub

  Dim Book_Array(5 To 18) As Variant
    Dim temp As Variant
    Dim i As Long, j As Long

 For k = 5 To 18
        Book_Array(k) = Range("B" & k).Value
    Next k

    i = LBound(Book_Array)
    Do Until i > UBound(Book_Array)
        j = i + 1
        Do Until j > UBound(Book_Array)

If Book_Array(i) > Book_Array(j) Then
                temp = Book_Array(j)
                Book_Array(j) = Book_Array(i)
                Book_Array(i) = temp

          End If
            j = j + 1
        Loop
        i = i + 1
    Loop

    Range("B5:B18") = Application.Transpose(Book_Array)

After Running the code, the dataset will be sorted as shown in the below image.

4. Sort Array in Reverse Order

We can also sort arrays in reverse order i.e. in descending order. It can be easily done just by changing the condition within the loop. 

For this procedure, we will use the same dataset. Also, we will use almost the same code using the For and If loops. Only the condition will be reversed.

Copy the following code in a new Module and Click on the Run button.

VBA Code for Sorting in Reverse Order:

Sub Sort_Array_Reverse()
    Dim Book_Array(5 To 18) As Variant
    For k = 5 To 18
        Book_Array(k) = Range("B" & k).Value
    Next k
    For i = LBound(Book_Array) To UBound(Book_Array) - 1
        For j = i + 1 To UBound(Book_Array)
            If Book_Array(i) < Book_Array(j) Then
                temp = Book_Array(j)
                Book_Array(j) = Book_Array(i)
                Book_Array(i) = temp
            End If
        Next j
    Next i
    Range("B5:B18") = Application.Transpose(Book_Array)
End Sub

Dim Book_Array(5 To 18) As Variant 

   For k = 5 To 18
        Book_Array(k) = Range("B" & k).Value
    Next k

    For i = LBound(Book_Array) To UBound(Book_Array) - 1
        For j = i + 1 To UBound(Book_Array)

            If Book_Array(i) < Book_Array(j) Then
                temp = Book_Array(j)
                Book_Array(j) = Book_Array(i)
                Book_Array(i) = temp

            End If
        Next j
    Next i

    Range("B5:B18") = Application.Transpose(Book_Array)

5. How to Sort Multidimensional Array

So far we have only seen a one-dimensional array. Now, let’s sort a multidimensional array. We are going to show you how we can sort a two-dimensional array with Excel VBA.

For this procedure, we have taken this dataset of famous Book Title names and their Authors’ names. We want the book names to be sorted along with the corresponding Author’s name.

To Sort Multidimensional Array, Copy the following Code in a new Module and Click on the Run button.

VBA Code to Sort Multidimensional Array:

Sub Sort_Multidimensional_Array()
    Dim lastRow As Long
    Dim i As Long
    Dim swapped As Boolean
    Dim tempTitle As String
    Dim tempAuthor As String
    Dim myArray() As Variant
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet10")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    ReDim myArray(0 To lastRow - 5) 
    For i = 0 To lastRow - 5
        myArray(i) = Array(ws.Cells(i + 5, "B").Value, ws.Cells(i + 5, "C").Value)
    Next i
    For i = LBound(myArray) To UBound(myArray) - 1
        swapped = False
        For j = LBound(myArray) To UBound(myArray) - i - 1
            If StrComp(myArray(j)(0), myArray(j + 1)(0), vbTextCompare) > 0 Then
                tempTitle = myArray(j)(0)
                tempAuthor = myArray(j)(1)
                myArray(j)(0) = myArray(j + 1)(0)
                myArray(j)(1) = myArray(j + 1)(1)
                myArray(j + 1)(0) = tempTitle
                myArray(j + 1)(1) = tempAuthor
                swapped = True
                End If
        Next j
        If Not swapped Then Exit For
    Next i
    For i = LBound(myArray) To UBound(myArray)
        ws.Cells(i + 5, 2).Value = myArray(i)(0)
        ws.Cells(i + 5, 3).Value = myArray(i)(1)
    Next i
End Sub

    Dim lastRow As Long
    Dim i As Long
    Dim swapped As Boolean
    Dim tempTitle As String
    Dim tempAuthor As String
    Dim myArray() As Variant

   Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet10")

    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    ReDim myArray(0 To lastRow - 5)

    For i = 0 To lastRow - 5
        myArray(i) = Array(ws.Cells(i + 5, "B").Value, ws.Cells(i + 5, "C").Value)
    Next i

   For i = LBound(myArray) To UBound(myArray) - 1
        swapped = False

For j = LBound(myArray) To UBound(myArray) - i - 1

If StrComp(myArray(j)(0), myArray(j + 1)(0), vbTextCompare) > 0 Then

              tempTitle = myArray(j)(0)
                tempAuthor = myArray(j)(1)
                myArray(j)(0) = myArray(j + 1)(0)
                myArray(j)(1) = myArray(j + 1)(1)
                myArray(j + 1)(0) = tempTitle
                myArray(j + 1)(1) = tempAuthor

   swapped = True

            End If
        Next j
        If Not swapped Then Exit For
    Next i

    For i = LBound(myArray) To UBound(myArray)
        ws.Cells(i + 5, 2).Value = myArray(i)(0)
        ws.Cells(i + 5, 3).Value = myArray(i)(1)
    Next i

After running the code, you can see the Book names are sorted alphabetically along with their Author names.

How to Sort Array Numerically Using VBA in Excel

So far, we only sorted the array alphabetically. Now, let’s sort them numerically. 

For the procedure, we have taken this simple dataset of random years. Using proper VBA Code we will sort this numerical data.

Copy the following Code in a new Module and Click on the Run button.

VBA Code to Sort Array Numerically:

Sub Sort_Array_Numerically()
    Dim Num_Array(5 To 18) As Variant
    For k = 5 To 18
        Num_Array(k) = Range("B" & k).Value
    Next k
   i = LBound(Num_Array)
    Do While i <= UBound(Num_Array)
        j = i + 1
        Do While j <= UBound(Num_Array)
            If Num_Array(i) > Num_Array(j) Then
                temp = Num_Array(j)
                Num_Array(j) = Num_Array(i)
                Num_Array(i) = temp
            End If
            j = j + 1
        Loop
        i = i + 1
    Loop
    Range("B5:B18") = Application.Transpose(Num_Array)
End Sub

    Dim Num_Array(5 To 18) As Variant

    For k = 5 To 18
        Num_Array(k) = Range("B" & k).Value
    Next k

   i = LBound(Num_Array)
    Do While i <= UBound(Num_Array)
        j = i + 1
        Do While j <= UBound(Num_Array)

         If Num_Array(i) > Num_Array(j) Then
                temp = Num_Array(j)
                Num_Array(j) = Num_Array(i)
                Num_Array(i) = temp

End If
            j = j + 1
        Loop
        i = i + 1
    Loop

    Range("B5:B18") = Application.Transpose(Num_Array)

So, after running the code, you can see it has sorted all the numeric values as the below image shows.

How to Sort Array by Date in Excel VBA

Now, let’s sort Array by date using Excel VBA. We can use essentially the same code as previously shown. We will perform this procedure with the Do While loop.

For this we have taken this simple dataset that includes random Dates. 

Copy the following Code in a new Module and Click on the Run button.

VBA Code to Sort Array by Date:

Sub Sort_Array_by_Date()  
    Dim Date_Array(5 To 18) As Variant
    For k = 5 To 18
        Date_Array(k) = Range("B" & k).Value
    Next k
   i = LBound(Date_Array)
    Do While i <= UBound(Date_Array)
        j = i + 1
        Do While j <= UBound(Date_Array)
            If Date_Array(i) > Date_Array(j) Then
                temp = Date_Array(j)
                Date_Array(j) = Date_Array(i)
                Date_Array(i) = temp
            End If
            j = j + 1
        Loop
        i = i + 1
    Loop    
    Range("B5:B18") = Application.Transpose(Date_Array)
End Sub

    Dim Date_Array(5 To 18) As Variant

    For k = 5 To 18
        Date_Array(k) = Range("B" & k).Value
    Next k

   i = LBound(Date_Array)
    Do While i <= UBound(Date_Array)
        j = i + 1
        Do While j <= UBound(Date_Array)

            If Date_Array(i) > Date_Array(j) Then
                temp = Date_Array(j)
                Date_Array(j) = Date_Array(i)
                Date_Array(i) = temp

            End If
            j = j + 1
        Loop
        i = i + 1
    Loop

    Range("B5:B18") = Application.Transpose(Date_Array)

After running the code, you can see it has sorted the dataset by date.

Frequently Asked Questions (FAQs)

• How do I sort a range of cells alphabetically in Excel?

Ans: You can use the sort method of the Range object to sort a range of cells alphabetically.

Here is a simple code that you can use:

Sub Sort_range_alphabetically()
Range(B5:B18).Sort Key1:=Range(B5:B18).Columns(1), Order1:=xlAscending, _ Header:=xlNo
End Sub

• How do I filter multiple columns in VBA?

Ans: To filter multi[le fields or columns use the following code.

(Range("A1"). AutoFilter Field:=3, Criteria1:="Enter Criteria Here" )

 and change the number of your desired field and the criteria.

• How do I sort an array in Excel VBA?

Ans: To sort an array in VBA, you need to write a code where you can match the first element of the array with the next one and inter-change them if the first one needs to come before. You need a FOR loop (For Next) for this and the Lbound and UBound functions.

Things to Remember

1. Make sure your Array is properly specified. If the Array size is not properly specified, you may get a runtime error.

2. Sorting a 1D array is quite straightforward, but sorting a 2D array by a specific column requires additional steps to extract the column as a 1D column. 

3. Before running any sorting code, make sure you have backed up your dataset properly. Otherwise, you will lose the original order.

4. To sort in the reverse order you just have to reverse the condition inscribed in the loop.

Conclusion

Thank you for making it this far. We have shown you several methods as to how you can sort Array alphabetically using Excel VBA. Hope, you find the content of this article useful. If there are further queries or suggestions, feel free to mention them in the comment section. For further knowledge, you can visit our website ExcelDemy.com.