Sorting 2 dimensional variant array in VBA

Question

I am new to VBA (I wish I could stick to Javascript) and I have a problem with sorting an array. I have this 2 dimensional array in keeping track of player scores like this

P1 P2 P3 P4 P5 P6 P7 P8 P9
23 12 34 11 21 56 32 35 27

And I would like to sort the array so the players are in order of scores from lowest to highest like this

P4 P2 P5 P1 P9 P7 P3 P8 P6
11 12 21 23 27 32 34 35 56

I haven't got a clue how to do this in VBA. I tried looking for a sort function, to no avail. Do I need to include some library or something?

Does anyone know how to tackle this problem if a library isn't available to do this?

Regards Crouz

[This should get you started](http://www.cpearson.com/excel/SortingArrays.aspx), it outlines two options. — Automate This, Apr 13 '15 at 16:09
The linked QuickSort answers are easily adaptable to a 2D array. If you get stuck with the adaptation, ask a better quality Q abot that. — chris neilsen, Apr 14 '15 at 06:50

score 1 · Accepted Answer · answered Apr 13 '15 at 16:17

Place your data in A1 through I2:

and then run this macro:

Sub Macro4()
    Range("A1:I2").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:I2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:I2")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

NOTE:

This is right from the Macro Recorder.

thanks for the info. I shall use the Macro recorder in the future, it's a gem. — Crouzilles, Apr 14 '15 at 15:28

score -1 · Answer 2 · edited May 23 '17 at 11:43

-1

I checked, and sure enough, some of the SO champs had already put together QuickSort routines in VBA. Here's a link:

VBA array sort function?

edited May 23 '17 at 11:43

Community

1
1

answered Apr 14 '15 at 05:17

Scott Bercaw

44
2

Sorting 2 dimensional variant array in VBA

2 Answers2