# 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.

## 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``````

🔎 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. ### 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``````

🔎 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. ### 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``````

🔎 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. ### 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``````

🔎 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. ### 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``````

🔎 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. ## 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``````

🔎 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. ## 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``````

🔎 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. • 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()
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

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 Advanced Excel Exercises with Solutions PDF  