Excel Sort

Excel Sort is definitely the fastest.
This transfers the array to a worksheet, sorts the array and then transfers it back to the array.

Sub TestSort() 
Dim avTesting() As Variant
   avTesting = Array(45, 30, 25, 15, 10, 5, 40, 20, 35, 50)
   Call Array_ExcelSort(avTesting)
   Stop
End Sub
Public Sub Array_ExcelSort(ByRef vArrayName As Variant) 
Dim vReturnArray As Variant
Dim lLower As Integer
Dim lUpper As Long
Dim i As Long

Dim FirstCell As Excel.Range
Dim LastCell As Excel.Range
Dim CurrCell As Excel.Range
Dim FillRange As Excel.Range
    
    lLower = LBound(vArrayName, 1)
    lUpper = UBound(vArrayName, 1)
    
    Set FirstCell = Sheets("Sheet1").Cells(1, 1)
    Set LastCell = Sheets("Sheet1").Cells(lUpper + 1, 1)
    Set FillRange = Range(FirstCell, LastCell)
    
'Application.ScreenUpdating = False
    ReDim vReturnArray(lLower To lUpper)
    vArrayName = Application.WorksheetFunction.Transpose(vArrayName)
    FillRange.Value = vArrayName
    FirstCell.CurrentRegion.Sort Key1:=FirstCell, Order1:=xlAscending, Orientation:=xlTopToBottom
    For i = lLower To lUpper
        vReturnArray(i) = FirstCell.Offset(i, 0)
    Next i
    FillRange.Clear
'Application.ScreenUpdating = True
    vArrayName = vReturnArray
End Sub

© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext