How to sort an array, one or multiple columns in excel VBA?

Please see the following code for sorting data A-Z, Z-A, or any other multi sorting combination that you wish.

Surprisingly there is no built in VBA syntax for this, which leaves you

with a few alternatives. Create your own custom sorting functions or script based on looping through your data array or matrix by incorporating comparison functions. The downside with this is that when you work with more data, this will result in more looping.. so not exactly super ideal from a speed perspective. Alternatively, and my favorite way is to sort the data on the worksheet by using the built in sort function there. By doing it this way there is not much code involved. Mostly, Range specification and what you want to sort on, and in which order you wish to sort the information on.

My Worksheet

Code

In order to not ruin this lovely matrix with data, I will first copy the matrix over to the right in order to run different sorting scenarios on my copied tabled.

​ '********************************Move Matrix to the right****************************

'Read in Array
MyArray = Range("A1:C11")

'Print out Array if I know my output dimensions
Range("F1:H11") = MyArray


'Print out Array if I don't know my output dimensions
Dim LastCell As Range

y = UBound(MyArray, 1) - 1 'My Y coordinate
x = UBound(MyArray, 2) - 1 'My X coordinate
Set LastCell = Range("F1").Offset(y, x)

'Print out my last cell address without the '$' signs which (False,False) provides.
'No need to remove the '$' signs, but looks more consistent.
'Debug.Print LastCell.Address(False, False)
​
'Print out array
Range("F1:" & LastCell.Address(False, False)) = MyArray

'Creating a range variable for ease of use below
Dim MyTable As Range
Set MyTable = Range("F1:" & LastCell.Address(False, False))

'**********************************Single column sort********************************

'This sorts ascending without header on a defined range.

'Sorts based on IMDB ranking starting from row 2 as the header range is excluded.
Range("F2:H11").Sort Key1:=Range("H2"), Order1:=xlAscending

'This sorts ascending without header on a non defined range with multiple columns.
Range("F2", Range("F2").End(xlDown).End(xlToRight)).Sort Key1:=Range("H2"), Order1:=xlAscending

'This sorts descending with header on a defined range.
'Sorts based on IMDB ranking starting from row 2 as the header range is excluded.
Range("F1:H11").Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlYes

'Alternatively we can just use our range variable 'MyTable' instead of specifying the range.
MyTable.Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlYes

'This sorts ascending without header on a non defined downward range (to be used on single column data).
'Note that only the column H is sorted, which means that the movies are mixed up.
Range("H2", Range("H2").End(xlDown)).Sort Key1:=Range("H2"), Order1:=xlAscending

'********************************Multiple column sort**************************************

'Sorting on 2 columns, first on IMDB Ranking and then Year.
With ActiveSheet.Sort
     .SetRange MyTable
     .SortFields.Add Key:=Range("H1"), Order:=xlAscending
     .SortFields.Add Key:=Range("G1"), Order:=xlAscending
     .Header = xlYes
     .Apply
End With


'Sorting on 2 columns, first on IMDB Ranking and then Year.
'Using the below instead of specifying the exact column.
'Starting positition for MyTable range is:

Debug.Print MyTable.Cells(1, 1).Address

'Second column for MyTable range is:
Debug.Print MyTable.Cells(1, 2).Address

With ActiveSheet.Sort
     .SetRange MyTable
     .SortFields.Add Key:=Range(MyTable.Cells(1, 2).Address), Order:=xlAscending
     .SortFields.Add Key:=Range(MyTable.Cells(1, 3).Address), Order:=xlAscending
     .Header = xlYes
     .Apply
End With

Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba

If you have found this article or website helpful. Please show your support by visiting the shop below.

Pls Fix Thx unisex Sweatshirt

Buy Now