Excel Vba Sort Array – Quick Trick using built in function

VBA Array Sort – Using Excel Sort

In this page, we have given a Excel vba code snippet that works well for sorting array of 1 dimension.

You can also customize & extend this code to work for 2 dimension array as well. We will publish the code for 2D array sort, once we are done with the testing.

How does this Excel Vba Sort Array works?

1. Create a temporary worksheet.
2. Copy array to that Excel worksheet.
3. Sort the Range in Excel.
4. Convert sorted range back to Array.

Yes. We are going to use Excel’s inbuilt sort option here. Lets see the code now.

Sub ExcelVbaSortArray()
    'Declare Array as variant & Range
    Dim sheetSort As Worksheet
    Dim vSortArray As Variant
    Dim rSortRange As Range
    Dim vItemsInArray As Double
    
    'Assign values to array
    vSortArray = VBA.Split("a,b,s,c,d,f,e,g,h,j", ",", , vbTextCompare)
    vItemsInArray = UBound(vSortArray) + 1
    
    'Create Temporary Sheet to Sort
    Set sheetSort = Worksheets.Add(after:=Sheets(Sheets.Count))
    Set rSortRange = sheetSort.Range("A1:A" & vItemsInArray)
    rSortRange = Application.Transpose(vSortArray)
    
    'Sort values in Array
    rSortRange.Sort rSortRange
    
    'Convert Range to Array
    vSortArray = Application.Transpose(rSortRange.Value)
    Debug.Print VBA.Join(vSortArray, ",")
    
    'Delete Sheet
    Application.DisplayAlerts = False
    sheetSort.Delete
    Application.DisplayAlerts = True
    Set sheetSort = Nothing
End Sub

The above actually sort the array in alphabetic order. It get completed within micro seconds.

In the above code, We have used function Transpose. To know more about what it does, You have to read about how a Array is converted to Range and vise versa. Click here to know more about this conversion steps..

There are multiple sorting techniques available like bubble sort, heap sort, merge sort, selection sort & so on. But it is better to use Excel in built sort option in here. Because it is the fastest when working with Excel.