How to sort an Excel array in place

Question

I'm populating an Excel array using an array formula and want the results to be sorted. If I attempt to sort (using data/sort) I get the error "You can't change part of an array". The only way I can sort it is by copying the array and pasting it as values, then sorting that copy. Is there a better way.

Steps to replicate:

In a blank worksheet, select range A1:A10 and insert an array formula that gives numeric results, e.g. =RAND(). The range is populated with values. Try to sort the range using Data/Sort and you get the above error.

Answer 1

You don't need a loop.

Sub Win_Loss()
Dim RND_Array() As Variant
Dim n As Integer
RND_Array = Application.Sequence(1, 100, 1, 1)
RND_Array = WorksheetFunction.SortBy(RND_Array,_
WorksheetFunction.RandArray(RND_Array), True)
For n = 1 To 100
    Debug.Print RND_Array(n)
Next n
End Sub

Answer 2

You can use Chris Nielson's approach with a 2D array as a workaround. If your array is in A1:C10, create a duplicate of it with =SORT(A1:C10,,). The copy can easily be sorted as desired. You do have to have a second copy of the array, but you don't have to copy/paste values and sort each time. Here's an example: Array Sort in place Example

Answer 3

I needed something similar and found this thread. The solution I came up with is to add a helper column that references the array elements, then sort by that. For example, if the array is in A1:A10, add a column Z1:Z10 with the formulas =A1 down to =A10 (not an array). Then sort by column Z but don't include column A in the sort. The array in column A gets sorted "in place". Not sure if that will help in your situation.

Answer 4

"In place"? I doubt it's possible with excel function as it will cause self/circular referencing.

If you select "A1:A10" and enter an array(CSE) formula = Rand() then it can be sorted with following Array (CSE) formula in cell "D1" and copied down. This will sort the array in ascending order. To sort it in descending order replace ",1)%" with ",0)%"

=INDEX($A$1:$A$10,MATCH(SMALL(NPV(-RANK.EQ($A$1:$A$10,$A$1:$A$10,1)%,$A$1:$A$10)*100+ROW($A$1:$A$10),ROWS($A$1:A1)),NPV(-RANK.EQ($A$1:$A$10,$A$1:$A$10,1)%,$A$1:$A$10)*100+ROW($A$1:$A$10),0))

Reference to this answer from shrivallabha.redij

For "In place" array function refer to UDF below. Select "H1:H10" and enter Array (CSE) Formula =SortRandArr(H1:H10,1) for descending sort or =SortRandArr(H1:H10,0) for ascending sort.

Function SortRandArr(arrSizeRng As Range, Optional srtCriteria = 0)
'arrSizeRng is range of the same size of desired one dimensional array
'srtCriteria is criteria to sort; 0 or nothing for Ascending, Other digit for descending.
Application.Volatile
Dim Lb As Long, Ub As Long, i As Long, j As Long
Dim arr

ReDim arr(arrSizeRng.Cells.Count - 1)

For x = LBound(arr) To UBound(arr)
arr(x) = Round(Rnd(), 4)
Next

Lb = LBound(arr): Ub = UBound(arr)

If srtCriteria = 0 Then
    For i = Lb To Ub - 1
    For j = i + 1 To Ub
        If Val(arr(i)) > Val(arr(j)) Then
        strTemp = arr(i)
        arr(i) = arr(j)
        arr(j) = strTemp
        End If
    Next j
    Next i
Else
    For i = Lb To Ub - 1
    For j = i + 1 To Ub
        If Val(arr(i)) < Val(arr(j)) Then
        strTemp = arr(i)
        arr(i) = arr(j)
        arr(j) = strTemp
        End If
    Next j
    Next i
End If

SortRandArr = Application.Transpose(arr)

End Function

For one dimensional VBA sort referred to this link