How to order values on multiples columns

Question

I've trying to figure out how to order values on multiples columns but I cannot find an answer

Below is how the values are right now:

            A   B   C   D   E   F
Row(1)     20   1   3   5   2   4  
Row(2)     19  11  12  14  16   8

I would like to find a way to order them on the below way

            A   B   C   D   E   F
Row(1)      1   2   3   4   5   8
Row(2)     11  12  14  16  19  20

The above is just part of the huge table I have but I can only sort information by column or by row but not all the data

Any idea how to do it ?

Answer 1

You can make use of the System.Collections.ArrayList to flatten the two-dimensional range. The ArrayList object has a .Sort method (and also a .Reverse method, if you ever need that).

So, this approach captures the range, dumps it in to an ArrayList, sorts it (ascending) and then writes it back out to the original range:

Option Explicit
Sub foo()
Dim sel As Range

Dim arr As Variant, val As Variant
Dim lst As Object
Dim i As Long

'Simplistic case of capturing the range to operate against, modify if needed
Set sel = Application.InputBox("Please select the table (excluding headers) to sort", "Flatten & sort", Type:=8)
' Dump the range values in a 2-d array
arr = sel.Value

'Flatten the range/array in to the ArrayList object
Set lst = CreateObject("System.Collections.ArrayList")
For Each val In arr
    lst.Add val
Next

'Sort the ArrayList
lst.Sort
' If you ever need to reverse the list, you can do:
' lst.Reverse

' Dump the sorted ArrayList values back to the worksheet:
For i = 1 To lst.Count 
    sel(i) = lst.Item(i - 1) 
Next

End Sub

Before:

After: