excel vba sort code example

Example 1: excel vba array sort

'VBA does not include a built-in method for sorting arrays like many
'languages do.

'However, it is easy to have VBA create and use a .NET ArrayList for sorting.

'***THE MAJOR REASON TO USE ARRAYLIST IN VBA IS SORTING***

Set arrList = CreateObject("System.Collections.ArrayList")
    arrList.Add 4
    arrList.Add 2
    arrList.Add 1
    arrList.Add 3
  	arrList.Sort				'<--Sort() uses the QuickSort algorithm
  	arr = arrList.toArray		'<--toArray() creates a zero-based VBA array
 	MsgBox Join(arr, ",")		'<--displays: 1,2,3,4
  	arrList.Reverse				'<--Sort() uses the QuickSort algorithm
    arr = arrList.toArray
 	MsgBox Join(arr, ",")		'<--displays: 4,3,2,1

'ArrayList interrogation:  
MsgBox arrList.Count			'<--displays: 4.  Count() returns 0 if Stack empty
MsgBox arrList.Contains("Hi")	'<--displays: False

    arrlist.Remove 3
    arr = arrlist.toArray
    Debug.Print Join(arr, ",")	'<--displays: 4,2,1
  
  
'Clone the ArrayList:
Set arrlist2  = arrlist.Clone	'<--arrlist2 is not affected by ops on arrlist
    
'Empty the ArrayList:
arrlist.Clear  					'<--arrlist now empty, but can take a new Add()


'NB:    ArrayLists do not need to be dimensioned or redim'd like VBA arrays.    
'NB:	ArrayList elements can hold simple values as well as complex objects.
    
'Reference: 
'    https://docs.microsoft.com/en-us/dotnet/api/system.collections.arraylist

Example 2: vba sort the data Z to A

Sub sbSortDataInExcel()
    
    'Delcaring the strDataRange as range store the target range to sort
        Dim strDataRange As Range
    'Delcaring the keyRange as range store the Sort key range to sort by
        Dim keyRange As Range
    
    'Assigning the target sort Range to strDataRange
        Set strDataRange = Range("A1:D10")
    'Assigning the sort key Range to keyRange
        Set keyRange = Range("A1")
    'Sorting the data using range objects and Sort method
        strDataRange.Sort Key1:=keyRange, Order1:=xlAscending
        
    End Sub

Example 3: vba sort the data Z to A

Sub sb_VBA_Sort_Data_Ascending()
Range("A1:D10").Sort _
Key1:=Range("A1"), Order1:=xlAscending
End Sub