vba - Sort range without sorting it in a spreadsheet

Question

Question is about sorting data in VBA. Suppose I have a Range("A1:A10") which I want to sort in ascending order. However, I do not want any changes in my spreadsheet (so all the calculations are made within a VBA code). The output of the operation should be a NewRange where all the numbers are sorted.

Has someone ideas about this problem?

Answer 1

Here is a very simple little routine to sort a two-dimensional array such as a range:

Option Base 1
Option Explicit

Function SortThisArray(aryToSort)

Dim i As Long
Dim j As Long
Dim strTemp As String

For i = LBound(aryToSort) To UBound(aryToSort) - 1
    For j = i + 1 To UBound(aryToSort)
        If aryToSort(i, 1) > aryToSort(j, 1) Then
            strTemp = aryToSort(i, 1)
            aryToSort(i, 1) = aryToSort(j, 1)
            aryToSort(j, 1) = strTemp
        End If
    Next j
Next i

SortThisArray = aryToSort

End Function

How to use this sort function:

Sub tmpSO()

Dim aryToSort As Variant

aryToSort = Worksheets(1).Range("C3:D9").Value2    ' Input
aryToSort = SortThisArray(aryToSort)               ' sort it
Worksheets(1).Range("G3:H9").Value2 = aryToSort    ' Output

End Sub

Notes:

1. The range sorted here is on Worksheet(1) in the Range("C3:D9") and the output is going on the same sheet into Range("G3:H9")
2. The range will be sorted in ascending order.
3. The range will be sorted based on the first column (here column C). If you wish to sort for another column then you just have to change all the aryToSort(i, 1) and aryToSort(j, 1) to which ever column you wish to sort. For example by column 2: aryToSort(i, 2) and aryToSort(j, 2).

UPDATE:

If you prefer to use the above as a function then this is also possible like this:

Option Base 1
Option Explicit

Function SortThisArray(rngToSort As range)

Dim i As Long
Dim j As Long
Dim strTemp As String
Dim aryToSort As Variant

aryToSort = rngToSort.Value2
For i = LBound(aryToSort) To UBound(aryToSort) - 1
    For j = i + 1 To UBound(aryToSort)
        If aryToSort(i, 1) > aryToSort(j, 1) Then
            strTemp = aryToSort(i, 1)
            aryToSort(i, 1) = aryToSort(j, 1)
            aryToSort(j, 1) = strTemp
        End If
    Next j
Next i

SortThisArray = aryToSort

End Function

And this is how you would use the function: