Excel VBA How to sort a single-row Range

Question

I have a Range defined as

Set myRange=Range("$E$10,$G$10,$I$10")

The values in these cells are -1, -1.2, 1 When I iterate over the range I get the values printed in order of -1, -1.2, 1

I would like sort this range so that when I iterate over the range I would see: -1.2, -1, 1

I do not want to reorganize these cells in the actual worksheet.

I am basically trying to mimic a sort function in a normal programming language, but for Excel range, where I expect the 'cells' to be re-arranged within the range data structure

I have tried a naive

myRange.Sort key1:=myRange.Item(1, 1), order1:=xlAscending, Header:=xlNo

But it does not do anything

@Tim, yep -- it looks like the suggested answers below are doing essentially that: extracting values from the range, putting them into another data structure, sorting that. It seems like I will have to loose the cell address during the extraction/sorting process -- unless I build a custom mechanism that remembers the cell address after extract/sorting step. — V P
– V P, Commented Nov 15, 2016 at 3:06
Instead of extracting the values into an array, you would just extract each cell. Seems like the answers below could easily be adjusted to sort the array based on its element's values — Tim Williams
– Tim Williams, Commented Nov 15, 2016 at 3:53

score 3 · Accepted Answer · 2016-11-14 00:08:15Z

3

Excel will not sort a non-contiguous range.

But with the using an ArrayList to sort the values it is easy to get the ranges values in order. Using SortAnyRange with it's Desc parameter set to true will sort the range in descending order.

WINDOWS ONLY

Sub TestSortAnyRange()
    SortAnyRange Range("$E$10,$G$10,$I$10")
End Sub

Sub SortAnyRange(Target As Range, Optional Desc As Boolean)
    Dim r As Range
    Dim list As Object
    Set list = CreateObject("System.Collections.ArrayList")
    For Each r In Target
        list.Add r.Value
    Next

    list.Sort

    If Desc Then list.Reverse

    For Each r In Target
        r.Value = list(0)
        list.Remove list(0)
    Next

End Sub

MAC or WINDOWS

The OP requested for a subroutine that will work on either a Mac or a Windows platform. For this reason, I refactored my code replacing the ArrayList (not available on mac) with an array and a BubbleSort routine.

Sub SortAnyRange(Target As Range, Optional Desc As Boolean)
    Dim r As Range
    Dim list
    Dim i As Long, j As Long

    ReDim list(0 To Target.Cells.Count - 1)

    For Each r In Target
      list(i) = r.Value
      i = i + 1
    Next

    For i = LBound(list) To UBound(list)
        For j = i + 1 To UBound(list)
            If list(i) > list(j) Then
                SrtTemp = list(j)
                list(j) = list(i)
                list(i) = SrtTemp
            End If
        Next j
    Next i

    i = IIf(Desc, UBound(list), 0)

    For Each r In Target
        r.Value = list(i)
        i = i + IIf(Desc, -1, 1)
    Next

End Sub

edited Nov 14, 2016 at 0:08

answered Nov 13, 2016 at 9:22

user6432984

Sign up to request clarification or add additional context in comments.

3 Comments

V P Over a year ago

Thank you. Will "System.Collections.ArrayList" or "System.Collections.SortedList" be available on Mac Excel 2013+ ? Or is this powerful .NET integration mechanism is only available on Windows? (I have some users to are still on Mac )

V P Over a year ago

Thx for confirming that Excel on MacOs does not have access to .NET. And also thx for offering workaround with manually implementing a bubblesort. Unfortunately I am getting an error/exception on last r.value = list(i) . (I am using Desc=True).

user6432984 Over a year ago

@VP Mac must be Option Base 1`. I updated my answer.

user3598756 · Accepted Answer · 2016-11-14 10:53:54Z

0

edited after OP clarified about target OS (both Windows and MAC OS) and he/she actually wanted cells to be "reorganized"

I'd use excel Sort() method of Range object and exploit all of its in-built capabilities

To this end I'll use a "helper" range, to put passed range values in, sort it, and write sorted values from back to passed range, as follows:

Sub OrderRange(rng As Range)
    Dim cell As Range
    Dim iCell As Long

    With rng.Parent.UsedRange
        With .Resize(1, 1).Offset(, .Columns.Count)
            For Each cell In rng '<-- fill "helper" range with passed range values
                .Offset(iCell).Value = cell.Value
                iCell = iCell + 1
            Next cell
            .Resize(iCell).Sort key1:=.Range("A1"), order1:=xlAscending, Header:=xlNo '<-- sort "helper" range values
            iCell = 0
            For Each cell In rng '<-- write sorted "helper" range values back to passed range
                cell.Value = .Offset(iCell).Value
                iCell = iCell + 1
            Next cell
            .Resize(iCell).ClearContents '<--| clear "helper" range
        End With
    End With
End Sub

to be used like:

OrderRange Range("$E$10,$G$10,$I$10")

edited Nov 14, 2016 at 10:53

answered Nov 13, 2016 at 7:52

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

1 Comment

user3598756 Over a year ago

@V P, did you get through it?

Collectives™ on Stack Overflow

Excel VBA How to sort a single-row Range

2 Answers 2

WINDOWS ONLY

MAC or WINDOWS

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

WINDOWS ONLY

MAC or WINDOWS

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related