Excel VBA Sort Array Alphabetically

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset for Sorting Array Alphabetically in Excel using VBA


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 to Sort Array alphabetically with For and If Loop in Excel

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

ūüĒé VBA Code Breakdown

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

The first line declares i, j, and k as Long variables.

The second line declares an Array to store values for sorting. It will take 14 elements.

And, the third line takes another variable temp as a variable. This will be used for swapping values during the sorting process.

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

This For loop is required for assigning values to the Book_Array.

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

These two lines initiate two For loops. The first one will loop from the lower bound to the second last element. And the second loop will loop from the second element to the upper bound one.

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

This portion is for bubble sorting. It compares two consecutive values. The smaller of the two will be sorted first.

End If
Next j
Next i

The If statement is ended and the program will move to the next pair of elements.

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

This line is for changing the horizontal array to a vertical range.

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

Output of Alphabetically Sorted Array using VBA in Excel


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 to Sort Array with While Loop

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

ūüĒé VBA Code Breakdown

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

First, we have declared Book_Array as an Array that can take 14 elements. And, we also have taken other variables.

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

This portion is for assigning data to the Book_Array.

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

The value of i is set as the lower bound of the array and initiates a While loop from the lower bound to the upper bound.

Then, it initiates another While loop from j= i+1 to the upper bound.

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

 This portion is doing bubble sorting. It will compare two consecutive values and sort the smaller value. 

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

This section ends the If statement. Then, it moves the program to the next pair.

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

This line is for changing the horizontal array into a vertical range.

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

Output of Sorted Array with While Loop


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 to Sort Array with Do Until Loop

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

ūüĒé VBA Code Breakdown:

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

First, we have declared Book_Array as an Array that can take 14 elements. And, we also have taken other variables.

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

This portion is for assigning data to the Book_Array.

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

The value of i is set as the lower bound of the Book_Array and initiates a Do Until loop from the lower bound to the upper bound.

Then, it initiates another Do Until loop from j= i+1 to the upper bound of the Book_Array.  

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

This portion is doing bubble sorting. It will compare two consecutive values and sort the smaller value.

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

This section ends the If statement. Then, it moves the program to the next pair.

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

This line is for changing the horizontal Array into a vertical range.

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

Output of Sorted Array with Do Until Loop


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 to Sort Array in Reverse Order

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

ūüĒé VBA Code Breakdown:

Dim Book_Array(5 To 18) As Variant 

First, we have declared Book_Array as an Array that can take 14 elements.

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

 This portion is for assigning data to the Book_Array.   

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

These two lines initiate two For loops. The first one will loop from the lower bound to the second last element. And the second loop will loop from the second element to the upper bound one.

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

This portion is for bubble sorting. It compares two consecutive values. The larger of the two will be sorted first.

            End If
        Next j
    Next i

The If statement is ended and the program will move to the next pair of elements.  

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

This line is for changing the horizontal array to a vertical range.

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

Output of Sorted Array in Reverse Order


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.

Dataset for Multidimensional Sorting

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

VBA Code for Multidimensional Sorting

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

ūüĒé VBA Code Brekdown:

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

    First, we declared proper variables. Also, we have taken myArray() as an unspecified Array.

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

 Then, these 2 lines are for activating the worksheet in which we have to perform sorting.

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

The first line counts the last non-blank row in the worksheet and sets the row number to last row.

The second line specifies the Array length. As our sorting data starts from row number 5, we have set the length from row number 5 to the last row.

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

 This loop is for assigning data to myArray. 

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

Initiates a For loop where the value of i ranges from the lower bound to the second last element of the Array. And, at the start, we have to keep the swapped mode Off. 

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

Then, another For loop where the value of j ranges from lower bound to unchecked element number.     

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

This line is for checking the smaller value between two elements.

              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

This section is for bubble sorting.       

   swapped = True

And, this line will swap the two elements if the condition is met.

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

This portion ends the If statement and moves the program to the next pair of elements.    

    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

This section takes the data from myArray and assigns them to the proper worksheet locations.

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

Output of Multidimensional Sorted Array


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.

Dataset to Sort Array Numerically

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

VBA Code for Sorting Array Numerically

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

ūüĒé VBA Code Breakdown:

    Dim Num_Array(5 To 18) As Variant

This line declares Num_Array with 14 elements.

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

This section assigns values in the Num_Array.

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

The first line sets the value of i as the lower bound.

The second line initiates a Do While loop from i to the last element of the Array.

Then, the value of j is set as i+1. 

And, another Do While loop is initiated from i to the last Array element.

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

This portion performs bubble sorting where the program compares two consecutive elements and sorts the smaller element.       

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

 This ends the loops and moves on to the next pair of elements.  

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

This line changes the horizontal array into a vertical range.

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

Output of Numerically Sorted Array


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. 

Dataset to Sort Array by Date

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

VBA Code for Sorting Array by Date

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

ūüĒé VBA Code Breakdown:

    Dim Date_Array(5 To 18) As Variant

This line declares Date_Array with 14 elements.

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

This section assigns values in the Date_Array. 

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

First line sets the value of i as the lower bound.

The second line initiates a Do While loop from i to the last element of the Array.

Then, the value of j is set as i + 1. 

And, another Do While loop is initiated from i to the last Array element.

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

This portion performs bubble sorting where the program compares two consecutive elements and sorts the smaller element.

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

This ends the loops and moves on to the next pair of elements.     

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

This line changes the horizontal array into a vertical range.

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

Output of Sorted Array 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.

Sourav Kundu

Sourav Kundu

Bio: Hi, I am Sourav Kundu. I live in Adabor, Dhaka. I graduated in Naval Architecture and Marine Engineering from BUET. I am really excited to be a part of SOFTEKO family. I want to develop my research skill and find innovative solutions for the given problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo