Excel's sorting is by far the fastest.
Sometimes when you assign an array to a worksheet range in Excel the data types of all the array elements are sometimes not the same.
It it better to force them all to strings using CStr().
' Sorts an array by transferring it to
' A worksheet and using Excel's sorting command
Dim First As Integer, Last As Long
Dim i As Long
Dim FirstCell As Range, LastCell As Range
Dim CurrCell As Range, FillRange As Range
First = LBound(list, 1)
Last = UBound(list, 1)
Set FirstCell = Sheets("Sheet1").Cells(1, 1)
Set LastCell = Sheets("Sheet1").Cells(Last, 1)
Set FillRange = Range(FirstCell, LastCell)
Application.ScreenUpdating = False
' Transfer the array to worksheet
FillRange.Value = list
' Sort the worksheet range
FirstCell.CurrentRegion.Sort Key1:=FirstCell, Order1:=xlAscending, Orientation:=xlTopToBottom
' Transfer range back to the array and clear range
For i = First To Last
list(i, 0) = FirstCell.Offset(i - 1, 0)
Application.ScreenUpdating = True
vArrayName1 = Application.Transpose(vArrayName) - doesn't seem to work
IsEmpty(ArrayName(1,1) - False - seems to be inconsistent !
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext