Excel VBA shines as a powerful programming language that allows its user to automate and personalize a variety of functions in Excel. Sorting data, particularly multidimensional arrays, is a popular operation in Excel. A collection of data with several rows and columns is referred to as a multidimensional array. Sorting can assist in the organization and analysis of data, making it simpler to read and comprehend. In this article, you’ll learn to use Excel VBA to sort multidimensional array.
With Excel VBA, sorting the data in a certain order is frequently important when working with multidimensional arrays. We’ll go over several sorting techniques and show you step-by-step how to use them in your VBA code. So, let’s get started.
Here we have attached a summarized video for your better understanding.
How to Launch VBA Editor in Excel
You can launch the VBA editor by following these simple methods. Here, we use the developer tab to insert the module where we write the code. Follow the simple steps to open the VBA editor.
- Initially, hover over to the Developer tab >> choose Visual Basic.
Note: By default, the Developer tab remains hidden. In that case, you have to enable the Developer tab.
- Then, the Visual Basic Editor launches and goes to the Insert tab >> Module >> Module1.
What Is a Multidimensional Array and How to Create It in Excel VBA?
An array with more than one dimension or axis is referred to as a multi-dimensional array. In other words, it has a table-like layout with rows and columns, and each cell has the capacity to store either a value or an item.
In Excel VBA, you can declare an array by specifying the number of dimensions and the size of each dimension using the Dim statement. The following code shows an example of creating a 2-dimensional array with 3 rows and 4 columns:
Dim array(1 To 3, 1 To 4) As IntegerThis generates an array with 3 rows and 4 columns. Using nested loops, you can also assign values.
For i = 1 To 3
For j = 1 To 4
myArray(i, j) = i * j
Next j
Next iThis code indicates the product of the row and column indexes for each cell in the array. If you take column 2 and row 2, for example, the value is 4. So, you can understand that the dimension is in the first bracket (). The first number is a row and the second is a column.
You can also put a different value in the box. Then it will be displayed in the dataset or in the Immediate window.
Excel VBA to Sort Multidimensional Array: 2 Examples
You can show the sorted data in the Immediate window, or you can sort the data in the worksheet. To show it in a worksheet, you need to populate the dataset with your data. Here, we have taken a dataset of “Information on ABC Car Company”. We will sort the Year and Price columns here.
Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.
1. Creating Multidimensional Array and Then Sorting
Here, we first create a random, unsorted dataset with the data we imputed in the array. We took 5 rows and 3 columns. Then this multidimensional array is sorted with the nested For Loops. Then the sorted data will be displayed in the Immediate window like the above image.
Sub SortMultiDimArray()
Dim arr(1 To 5, 1 To 3) As Variant
Dim i As Long, j As Long
'Populate the array with some data
arr(1, 1) = 5: arr(1, 2) = 3: arr(1, 3) = 7
arr(2, 1) = 1: arr(2, 2) = 9: arr(2, 3) = 2
arr(3, 1) = 6: arr(3, 2) = 8: arr(3, 3) = 4
arr(4, 1) = 2: arr(4, 2) = 4: arr(4, 3) = 9
arr(5, 1) = 3: arr(5, 2) = 1: arr(5, 3) = 8'Sort the array in ascending order by all columns
For j = 1 To 3
For i = 1 To 4
For k = i + 1 To 5
If arr(i, j) > arr(k, j) Then
Dim temp As Variant
temp = arr(i, j)
arr(i, j) = arr(k, j)
arr(k, j) = temp
End If
Next k
Next i
Next j
'Print the sorted array to the immediate window
For i = 1 To 5
For j = 1 To 3
Debug.Print arr(i, j);
Next j
Debug.Print vbNewLine;
Next i
End SubCode Breakdown:
- We have declared a two-dimensional array called arr where we put 5 rows and 3 columns and put some data in it.
- We have used nested loops in each column of the array. The outer loop iterates over each column of the array and the two inner loops iterate through each row of the column.
- The innermost loops specifically compare the current element with the next element. If the current element is greater than the next element, then the two elements are interchanged.
- This is the sorting process. After all this sorting the output is displayed in the Immediate window.
Basically, the code is designed to sort a two-dimensional array in ascending order. But we discourage you to use it as it is not very efficient for large arrays. But it is simple and understandable to implement in 2D arrays.
We have attached a video for your better visualization.
Read More: How to Sort Array with Excel VBA
2. Applying Bubble Sorting in Multidimensional Array
Bubble sorting is a simple algorithm that repeatedly iterates through the list or array that we need to sort. We need to sort them out when they are in the wrong order. You can perform bubble sorting in a multidimensional array. Here, we used bubble sorting to sort a 5 by 3 array. We generate a random integer with the VBA Rnd command. It will generate a random number from 1 to 100 and then the unsorted data will be sorted in the Immediate window.
Sub BubbleSortMultiDimensionalArray()
Dim arrData(1 To 5, 1 To 3) As Integer
Dim i As Long, j As Long, k As Long
Dim temp As Integer
' Populate array with random values
For i = 1 To 5
For j = 1 To 3
arrData(i, j) = Int(Rnd() * 100)
Next j
Next i
' Display unsorted array
Debug.Print "Unsorted Array:"
For i = 1 To 5
For j = 1 To 3
Debug.Print arrData(i, j);
Next j
Debug.Print vbCrLf;
Next i
' Sort array using bubble sort
For i = 1 To UBound(arrData, 1) - 1
For j = 1 To UBound(arrData, 1) - i
For k = 1 To UBound(arrData, 2)
If arrData(j, k) > arrData(j + 1, k) Then
' Swap values
temp = arrData(j, k)
arrData(j, k) = arrData(j + 1, k)
arrData(j + 1, k) = temp
End If
Next k
Next j
Next i
' Display sorted array
Debug.Print "Sorted Array:"
For i = 1 To 5
For j = 1 To 3
Debug.Print arrData(i, j);
Next j
Debug.Print vbCrLf;
Next i
End SubCode Breakdown:
- A sub-procedure called “Sub BubbleSortMultiDimensionalArray()” is declared in the first line of the code.
- The second line declares the “arrData” variable, a 2-dimensional integer array with 5 rows and 3
- The program declares three long integer variables, “i” “j,” and “k,” as well as a temporary variable, “temp“, for use in the sorting operation.
- The “For” loop beginning on line 7 uses the “Int(Rnd() * 100)” function to fill the array with random integer values.
- After that, the code uses nested “For” loops and the “Print” instruction to show the unsorted array in the current window.
- The 2-dimensional array is then sorted using the bubble sort method. The outer “For” loop, beginning on line 16, regulates how many times the array is passed through (i.e., it will iterate for a total of “n-1” times for an array of “n” elements).
- Beginning on line 17, the first nested “For” loop iterates over the array and compares nearby entries to see if they should be switched.
- Beginning on line 18, the second iteration of the “For” loop compares nearby entries as it traverses the array’s columns.
- The code switches the items using a temporary variable called “temp” if the comparison decides that they need to be switched.
- The “Print” command and nested For Loops are used to display the sorted array in the current window when sorting is finished.
- The sub-procedure is ended using the “End Sub” command.
You see the below video for a better understanding.
Read More: Excel VBA Sort Array Alphabetically
How to Sort Columns in Excel VBA
Multidimensional means more than one dimension. You can sort single or multiple columns with the help of VBA. You need to populate your dataset first. For sorting in the worksheet, you need to specify the range of the array. We have described both single and multiple columns here. So let’s jump into the discussion.
1. Sorting Single Column
Suppose, you want to sort a particular column with the help of VBA, you can do it after selecting that column and sorting in ascending or descending order. You can see in column E that the “Price” of the cars is sorted in ascending order. The VBA code first shows an InputBox to select a data range you want to sort, then it sorts it from top to bottom.
The following VBA code has done the job for us.
Sub SortSelectedRangeAscending()
Dim dataRange As Range
' Prompt the user to select a range using an input box
On Error Resume Next
Set dataRange = _
Application.InputBox(prompt:="Select a range to sort:", Type:=8)
On Error GoTo 0
' Check if the user canceled the input box
If dataRange Is Nothing Then
MsgBox "Range selection canceled."
Exit Sub
End If
' Sort the selected range in ascending order
With dataRange
.Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlYes
End With
End SubCode Breakdown:
- “Sub SortSelectedRangeAscending()” is a sub procedure that is declared in the first line of the code.
- The user-selected range will be stored in a Range variable called “dataRange“, which is declared in the second line.
- The “On Error Resume Next” statement instructs VBA to forgo any potential runtime errors that may arise while the next code is being executed.
- With the use of an input box, the “Set” statement asks the user to choose a range, then adds that range to the “dataRange” variable. The “Type:=8” argument instructs the input box to only accept input in the form of ranges.
- Any faults that happen after the “On Error GoTo 0” statement will be captured by VBA and shown to the user since it disables error handling at this point.
- By determining if the “dataRange” variable is empty, the “If” statement determines whether the user closed the input box (i.e., equal to “Nothing”). A message box informing the user that the range selection was abandoned will be shown if the variable is empty, and the subprocedure will end.
- Without repeatedly referencing the same object, a method or property may be applied to a given object using the “With” statement. The “With” statement is applied to the “dataRange” variable in this instance, which implies that any statements that follow it in the “With” block will work with the chosen range.
- Based on the values in the first column, the “.Sort” method is used to sort the chosen range in ascending order. The sort key should be the first cell in the range, as specified by the “Key1:=.Cells(1,1)” argument, and the sort order should be ascending as specified by the “Order1:=xlAscending” parameter. The range includes a header row that should also be sorted, according to the “Header:=xlYes” argument.
- Lastly, the End Sub statement finishes the sub-procedure.
To clarify your confusion watch the below video.
Read More: Excel VBA to Custom Sort
2. Sorting Multiple Columns
The VBA helps you to sort multiple columns as per your desire. When you use sorting, you can use ascending or descending order. In our dataset, we set column D in ascending order and column E in descending order. For this, you have to use two keys (Key 1 and key 2) where you need to set the order accordingly. We have selected the range first. You can also use an InputBox for a dynamic range as we use it in method 3.1.
Sub SortData()
Dim rngSort As Range
Set rngSort = Range("B5:F17")
' Sort by column B (ascending) and column C (descending)
With rngSort
.Sort Key1:=.Columns(3), Order1:=xlAscending, _
Key2:=.Columns(4), Order2:=xlDescending, _
Header:=xlNo, Orientation:=xlTopToBottom
End With
End SubCode Breakdown:
- A sub-procedure with the name “Sub SortData()” is declared in the first line of the code.
- The range to be sorted will be stored in a Range variable called “rngSort“, which is declared in the second line.
- The “Set” statement is used to assign the range “B5:F17” to the “rngSort” variable.
- By using the “With” statement, you may affect the “rngSort” range without referencing it more than once.
- The chosen range is sorted into two keys using the “.Sort” function. The first sort key should be the third column (column C) in the range, according to the “Key1:=.Columns(3)” option, and the “Order1:=xlAscending” parameter specifies that the sort order for this key should be ascending.
- The second sort key should be the fourth column (column D) in the range, according to the “Key2:=.Columns(4)” argument, and the “Order2:=xlDescending” option specifies that the sort order for this key should be descending.
- The range does not contain a header row that has to be sorted, according to the “Header:=xlNo” argument.
- The sort should be done from top to bottom, according to the “Orientation:=xlTopToBottom” argument.
- The “With” block is concluded with the “End With” declaration.
- The sub-procedure is ended using the “End Sub” command.
Here, we have attached a video of our method.
How to Sum Multidimensional Array in Excel VBA
We have a price column in our dataset. We want to find the sum of column E. For doing this, we have entered an argument in the VBA code where we put a sum command. This will estimate the sum of column E.
Sub SumMultiArrayInWorksheet()
Dim arr As Variant
Dim i As Long, j As Long
Dim sum As Double
arr = Range("E5:E17").Value ' Change the range as per your data
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
sum = sum + arr(i, j)
Next j
Next i
'Change the cell reference where you want to display the result
Range("E18").Value = sum
End SubCode Breakdown:
- Sub SumMultiArrayInWorksheet() – With this line, a new Sub-procedure with the name SumMultiArrayInWorksheet is defined.
- Dim arr As Variant – This line creates a variant variable with the name arr that will be used to hold a range of values.
- Then we define two variables with the names i and j that are of the data type Long. As loop counters, these variables will be employed.
- range = arr(“E5:E17”).Value – This line gives the arr variable the values in the range E5:E17.
- Since i = LBound(arr, 1) The line To UBound(arr, 1) initiates a loop that iterates through the rows of the arr array. The lower and upper boundaries of the array’s first dimension are provided by the LBound and UBound functions, respectively.
- j = LBound for(arr, 2) The line To UBound(arr, 2) initiates a loop that iterates across the columns of the arr array. The lower and upper boundaries of the array’s second dimension are provided by the LBound and UBound functions, respectively.
- sum = sum + arr(i, j) – This statement increases the value of the sum variable by the value of the current element in the arr array.
- Then theSum command calculates the sum and returns it in cell E18.
How to Sort Multidimensional Array Alphabetically in Excel VBA
Here, in the first dataset, the value of the Car Name is not sorted alphabetically. We will sort it alphabetically. We run a VBA code here to do so. When we run the code, the value in column B has been sorted alphabetically.
Sub SortMultiArrayAlphabetically()
Dim arr As Variant
Dim i As Long, j As Long
Dim numRows As Long, numCols As Long
arr = Range("B5:B17").Value ' Change the range as per your data
numRows = UBound(arr, 1)
numCols = UBound(arr, 2)
' Sort the array alphabetically by the first column
For i = 1 To numRows - 1
For j = i + 1 To numRows
If arr(i, 1) > arr(j, 1) Then
SwapRows arr, i, j, numCols
End If
Next j
Next i
' Write the sorted array back to the worksheet
Range("B5").Resize(numRows, numCols).Value = arr
End SubSub SwapRows(ByRef arr As Variant, ByVal i As Long, _
ByVal j As Long, ByVal numCols As Long)
' Swaps two rows in a 2-dimensional array
Dim temp As Variant
Dim k As Long
For k = 1 To numCols
temp = arr(i, k)
arr(i, k) = arr(j, k)
arr(j, k) = temp
Next k
End SubCode Breakdown:
- Firstly, we call a sub-procedure named SortMultiArrayAlphabetically(). Then we declared the variable.
- numRows and numCols are two variables of the data type Long that are declared in the line Dim numRows As Long, numCols As Long. The number of rows and columns in the arr array will be kept in these variables.
- range = arr(“B5:B17”).Value – This line gives the arr variable the values from B5 to B17.
- The number of rows in the arr array is determined by the formula numRows = UBound(arr, 1).
- This line sets the numCols variable to the number of columns in the arr array using the formula UBound(arr, 2).
- For i = 1 From the first row through the next-to-last row of the arr array, a loop called through numRows – 1 begins with this line.
- For j = i + 1 Beginning with the row that follows the current row and ending with the final row, the loop To numRows iterates over the rows of the arr array.
- Rows to swap: arr, i, j, numCols – This line invokes the SwapRows Sub function with the arguments arr array, indices of the two rows to be swapped, and the array’s number of columns.
- The inner loop over the rows comes to an end in the next j lines and the outer loop across the rows is completed on the next line, i.
- Range(“B5”).Resize(numRows, numCols).Value = arr – Starting in cell B5 and spanning the same number of rows and columns as the original array, this line sends the sorted arr array back to the worksheet.
- End Sub – With this line, the SortMultiArrayAlphabetically Sub-process is defined.
Frequently Asked Questions
1. How do I preserve the original order of a multidimensional array in Excel VBA after sorting?
Creating a different duplicate of the array before sorting and utilizing that copy to restore the original order after sorting are two ways to keep a multidimensional array’s original order in Excel VBA.
2. How do I sort a multidimensional array in Excel VBA based on the values in a specific column?
Using the Sort function and the column index as the Key parameter, you may order a multidimensional array in Excel VBA according to the values in a particular column. Using the values in the second column to sort a 2D array called myArray in ascending order, as an example.
Dim myArray(1 To 5, 1 To 3) As Variant
' Populate the array with data
' Sort the array based on values in the second column
Dim tempArray() As Variant
tempArray = myArray
Application.Sorted(tempArray, Key1:=2, Order1:=xlAscending, Header:=xlNo)3. Are there any limitations to sorting a multidimensional array in Excel VBA?
Yes, sorting a multidimensional array in Excel VBA has certain restrictions. For instance, it is impossible to sort an array that has objects or other arrays as elements, or one that has more than 65,536 rows or columns.
Things to Remember
- While declaring an array, you must determine the column and row number. Also, you need to define the data type as a Variant.
- The sort function modifies the real array. So, you need to keep the original data intact.
- At the time of sorting, you need to specify the order of the array in the first bracket.
- You can transpose the array using the Transpose function in VBA.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
Conclusion
So, this is all about our tutorial. We hope this will help you to understand the basics of multidimensional arrays, and you will be able to use Excel VBA to sort multidimensional array. Sorting is important for continuing your workflow smoothly. When dealing with a large dataset and wanting to find unnecessary and duplicate data, sorting is important. If you have any questions, please leave them in the comments section.
Related Articles
- Excel VBA to Sort Alphabetically
- Excel VBA to Sort a ComboBox List Alphabetically
- VBA to Sort Table in Excel
- Excel VBA to Sort in Descending Order
- [Fixed!!] VBA Sort Not Working in Excel
