Sort Range in absolute value

Question

I would like to sort a range in absolute value but keep the sign of the number.

I did a code that works fine, but I copy/paste values in another range and afterwards, I delete it.

I would like not to copy/paste in my sheet but just do it in memory.

How can I do that?

My range:

-1
2
3
-4
4.5

The expected result :

4.5
-4
3
2
-1

My code:

Sub Appel()

    For i = 1 To Range("D1").End(xlDown).Row
        Range("E" & i) = Abs(Range("D" & i).Value)
    Next i

    Range("E1", Range("D1").End(xlDown)).Sort Key1:=Range("E1"), 
    Order1:=xlDescending, Header:=xlNo

    Columns("E:E").Delete

End Sub

You could use a custom sort: `Application.AddCustomList ListArray: SortArray`, where sort array has been created from the `abs(values)` in your list and is [sorted](https://stackoverflow.com/questions/152319/vba-array-sort-function). — Cyril, Jun 12 '19 at 15:22

score 2 · Accepted Answer · answered Jun 12 '19 at 15:36

To do this in memory I would first load it to a single dimensional array.

Then I would create a specific function that does your custom sort on the array.

Once it's sorted, then simply set the value to that range from the sorted array.

Sub Appel()

    Dim TargetRange As Range
    Set TargetRange = Range("D1", Range("D" & Rows.Count).End(xlUp))

    'This returns as single dim array from a Range column
    Dim ColumnData As Variant
    ColumnData = Application.Transpose(TargetRange.Value)

    Dim SortedData As Variant
    SortedData = SortAbsoluteDecending(ColumnData)

    'Set value of range equal to the new sorted array.
    TargetRange.Value = Application.Transpose(SortedData)

End Sub

Public Function SortAbsoluteDecending(SourceArray As Variant) As Variant

    Dim OuterIndex As Long
    For OuterIndex = LBound(SourceArray) To UBound(SourceArray) - 1

        Dim InnerIndex As Long
        For InnerIndex = OuterIndex + 1 To UBound(SourceArray)

            If Abs(SourceArray(OuterIndex)) < Abs(SourceArray(InnerIndex)) Then
                Dim Temp As Variant
                Temp = SourceArray(InnerIndex)
                SourceArray(InnerIndex) = SourceArray(OuterIndex)
                SourceArray(OuterIndex) = Temp
            End If

        Next InnerIndex
    Next OuterIndex

    SortAbsoluteDecending = SourceArray

End Function

Sort Range in absolute value

1 Answers1