Excel VBA: Sort Method on Range Collection

[Alba Ra]

Hello,

I'm still trying to work out the same problem: how to process data from an Excel sheet (above all sort through it but in groups; will explain further down) to use in a Cluster Chart without storing it as data in an Excel sheet.

The problem at hand:
I have a table somewhat like this...

A             B        E   K   Y
4   Group One     Green    5   3   3
5                 Yellow   3   7   4
6                 Blue     7   6   2
7                 Grey     9   1   8
8   Group Two     High     1   5   1
9                 Low      2   6   6
10  Group Three   Small    5   1   2
11                Large    1   1   5

I left out the rows and columns that contain data irrelevant for the chart (like rows 1 to 3, columns C, D, F to J, et).

What I need to do is create (in this example) three cluster charts, one with the ranges A4:B11;E4:E11, the second with A4:B11;K4:K11 and the third with A4:B11;Y4:Y11. Data has to be sorted for each column, from row 4 to 7, from 8 to 9 and from 10 to 11, but has to appear in the same chart.

The data for the first would have to sorted like that:

A             B        E
4   Group One     Grey     9
5                 Blue     7
6                 Green    5
7                 Yellow   3
8   Group Two     Low      2
9                 High     1
10  Group Three   Small    5
11                Large    1

One of the reasons that I don't want to put the data in another range, sort it there and use that for the chart is that I have to create many charts, and I literally mean many.

I searched to internet, only to find merely half-solution like creating charts with a macro and attributing a range to Chart.SetSourceData but how to sort that range first (in those three group^s described above) I haven't figured out yet. (See my Excel VBA: Array to Range (for SetSourceData) thread.)
Or I tried to apply an SQL statement but couldn't figure how to achieve that. (See my SQL Query within Excel thread.)

Additionally I don't get neither the Sort method to work (naturally all examples imply the use on an actual range on a sheet and not a stored range collection. But the original datasheet may not be sorted!

I tried using Sort like this:

Dim rdt As Range
Dim r1 As Range
Dim myColl As Range
Set rdt = Range("B4:B30")
Set r1 = Range("G4:G30")
Set myColl = Union(rdt, r1)
myColl.Sort Key1:=[B]X[/B], Order1:=xlDescending, Key2:=[B]Y[/B], Order2:=xlAscending

But I don't know how to define the Key.

I also tried sorting through the range by using a function. It doesn't work either:

Public Function TriTableau(MyVar())
    Dim i As Integer
    Dim j As Integer
    Dim tempA
    Dim tempB
    For i = 1 To 26
        For j = 2 To 27
            If MyVar(i, 2) < MyVar(j, 2) Then
                tempA = MyVar(i, 1)
                tempB = MyVar(i, 2)
                MyVar(i, 1) = MyVar(j, 1)
                MyVar(i, 2) = MyVar(j, 2)
                MyVar(j, 1) = tempA
                MyVar(j, 2) = tempB
            End If
        Next j
    Next i
    TriTableau = MyVar
End Function

Which is adapted from a French book by Mikaël Bidault, Excel & VBA XP, published 2005.

Again, it won't work, neither on the stored range collection itself nor on an array created from the range.

And before you ask: I tried LINQ as well, the Visual Basic Editor has right from the first step a problem, when creating the query like this Dim evensQuery = From num In numbers Order By num Select num the editor stops at "=" expecting the end of the instruction.

As I'm trying to solve the problem for two days now, I'd really appreciate any help you could offer.