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?

Please show your effort to elicit response here. Pl note that SO is not a code for me service provider. It helps in sorting out specific problems encountered by the members. Please search for excel vba sort algorithms and then put effort to do something on your own . Here experts are too happy and keen to resolve specific problems in coding faced by its members. — skkakkar, May 15 '16 at 14:05
Well, can be done with formulas, can be done by manually copy-pasting and then sorting, or you can write a macro to do that for you. Your question is too broad as it is. — vacip, May 15 '16 at 14:10
There are any number of VBA sort routines you could use and easily find with an Internet search. Or you could just do all your calculations in your VBA array; write the results to NewRange and sort the results on the worksheet. If you run into problems with the code you develop, edit your original question to show that and be specific about the code. Also read HELP for how to ask a question; and how to provide an example. — Ron Rosenfeld, May 15 '16 at 14:10
Thanks for answers but I've done the search before. I found a lot of examples where sort is realized with changing ranges in spreadsheets. However, I did not find the example where I do not change spreadsheets' cells at all. That is why I asked here this question. — Alex, May 15 '16 at 21:29

Ralph · Answer 1 · 2016-05-15T20:18:32.460

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:

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")
The range will be sorted in ascending order.
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:

@Gary'sStudent Thanks, but I guess your simplistic approach still got you more up-votes. — Ralph, May 15 '16 at 20:12
Simple is good, but .................I like your code, I really like explanation, and I REALLY like the dynamic tutorial built into the answer!! — Gary's Student, May 15 '16 at 20:19
@Gary'sStudent Thanks and the update might help here http://stackoverflow.com/questions/37243152/sortind-2d-range-using-udf — Ralph, May 15 '16 at 20:22
The *UDF* approach is clearly the best if the data is frequently changed. — Gary's Student, May 15 '16 at 20:26
@Ralph, thanks for your answer! Although it is not I meant in my question, I think it is a great job, and many of us will find it helpful someday. Kudos! — Alex, May 15 '16 at 21:34
I had issues with `Dim strTemp As String` not sorting numbers properly. Wouldn't using `Dim strTemp As Variant` be much more flexible? — JCKE, Nov 08 '18 at 02:49

Gary's Student · Accepted Answer · 2016-05-15T22:16:50.260

This is just a sample that you may adapt to your needs, it uses B11:B20 as NewRange:

Sub SortElseWhere()
    Dim A As Range, NewRange As Range

    Set A = Range("A1:A10")
    Set NewRange = Range("B11:B20")
    A.Copy NewRange
    NewRange.Sort Key1:=NewRange(1, 1), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

The original cells are not sorted, they are merely copied to another location which is sorted.

EDIT#1:

In this version, NewRange is not a range of cells, but an internal array:

Sub SortElseWhere2()
    Dim A As Range, NewRange(1 To 10) As Variant
    Dim i As Long, strng As String
    i = 1
    Set A = Range("A1:A10")
    For Each aa In A
        NewRange(i) = aa
        i = i + 1
    Next aa

    Call aSort(NewRange)

    strng = Join(NewRange, " ")
    MsgBox strng

End Sub

Public Sub aSort(ByRef InOut)

    Dim i As Long, J As Long, Low As Long
    Dim Hi As Long, Temp As Variant

    Low = LBound(InOut)
    Hi = UBound(InOut)

    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For i = Low To Hi - J
          If InOut(i) > InOut(i + J) Then
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        For i = Hi - J To Low Step -1
          If InOut(i) > InOut(i + J) Then
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        J = J \ 2
    Loop
End Sub

Thanks! Do you have a solution when NewRange values are not pasted into a spreadsheet but only sorted within? — Alex, May 15 '16 at 21:46

score 2 · Answer 3 · answered May 15 '16 at 16:06

Here I am submitting slightly different sort routine.It sorts the 2nd column first then 1st column.

Function BubbleSort(TempArray() As Variant, SortIndex As Long)

    Dim blnNoSwaps As Boolean

    Dim lngItem As Long

    Dim vntTemp(1 To 2) As Variant

    Dim lngCol As Long

    Do

        blnNoSwaps = True

        For lngItem = LBound(TempArray) To UBound(TempArray) - 1

            If TempArray(lngItem, SortIndex) > TempArray(lngItem + 1, SortIndex) Then

                blnNoSwaps = False

                For lngCol = 1 To 2

                    vntTemp(lngCol) = TempArray(lngItem, lngCol)

                    TempArray(lngItem, lngCol) = TempArray(lngItem + 1, lngCol)

                    TempArray(lngItem + 1, lngCol) = vntTemp(lngCol)

                Next

            End If

        Next

    Loop While Not blnNoSwaps

End Function



Sub Test()

    Dim vntData() As Variant

    vntData = range("C3:D9")

    BubbleSort vntData, 2

    BubbleSort vntData, 1

    range("G3:H9") = vntData

End Sub

Results obtained from this routine are shown below.

Sort range without sorting it in a spreadsheet

3 Answers3

UPDATE:

Linked