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

How my worksheet looks like

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

"Icon made by Freepik from www.flaticon.com", "Icon made by Surang from www.flaticon.com", "Icon made by Pixel perfect from www.flaticon.com", "Icon made by Phatplus from www.flaticon.com"

vba insert into sql server, excel custom ribbon vba, vba code for sending email from excel sheet, hidden vba code in excel, VBA secret tricks