### Method 1 – Creating Multidimensional Array and Then Sorting

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. 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 Sub
```

**Code Breakdown**:

- We 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, and the two inner loops iterate through each row.
- 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**.

The code is designed to sort a two-dimensional array in ascending order. We discourage you from using it as it is not very efficient for large arrays. It is simple and understandable to implement in **2D** arrays.

We attached a video for your better visualization.

### Method 2 – Applying Bubble Sorting in Multidimensional Array

Bubble sorting is a simple algorithm that repeatedly iterates through the list or array we need to sort. Sort them out when they are in the wrong order. You can perform bubble sorting in a multidimensional array. 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 Sub
```

**Code 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. - 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 “**Fo**r” 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 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 can see the video below for a better understanding.

## How to Sort Columns in Excel VBA

### Method 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 it 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 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 Sub
```

**Code 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. - Using 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. - A method or property may be applied to a given object without repeatedly referencing the same 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 watch the below video.

### Method 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. 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 Sub
```

**Code 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.

We 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**. To do 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 Sub
```

**Code 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 the
**Sum**command calculates the sum and returns it in cell**E18**.

## How to Sort Multidimensional Array Alphabetically in Excel VBA

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

**Code Breakdown**:

- We call a sub-procedure named SortMultiArrayAlphabetically(). Then we declared the variable.
**numRows**and**numCols**are two variables of the data type Long 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,**the loop To numRows iterates over the rows of the arr array, beginning with the row that follows the current row and ending with the final row. - 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 ends 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.
- When 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.

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