SortingMultiDimensionArray

[ashish mehra]

Hi all,

The below code is sorting on column B in ascending order.

I want help in
1) inserting header too & then
2) would be able to sort on any column in case sort column number changes.

Sub SortingMultiDimensionArray()
'Writing Range to Dynamic Array
    Dim MyArray() As Variant
    Dim myRange As Range
    Dim LR As Long, wsName As String
   
    Sheet1.Activate
   
    MyArray = Range("A2", Range("A1").End(xlDown).End(xlToRight))
   
'Writing Dynamic Array to Range
   
    Worksheets.Add

    Set myRange = Range(ActiveCell, ActiveCell.Offset(UBound(MyArray, 1) - 1, UBound(MyArray, 2) - 1))
   
    myRange = MyArray
   
    LR = Cells(Rows.Count, 1).End(xlUp).Row

    'set the sort key, in this case sort by column B
    ActiveSheet.Sort.SortFields.Add Key:=Range("B" & LR), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   
    With ActiveSheet.Sort
        .SetRange myRange
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    Range("B2").Select

    Erase MyArray
End Sub

 

[jindon]

See if this is how you want.

Sub test()
    Dim a
    Const SortCol As Long = 2
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    With Sheets.Add.Cells(1).Resize(UBound(a, 1), UBound(a, 2))
        .Value = a
        .Sort .Cells(1, SortCol), 1, , , , , , 1
    End With
End Sub

 

[YasserKhalil]

That's awesome Mr. Jindon
I liked your code a lot
Can you please explain the parameters in this line

.Sort .Cells(1, SortCol), 1, , , , , , 1

 

[ashish mehra]

See if this is how you want.

Sub test()
    Dim a
    Const SortCol As Long = 2
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    With Sheets.Add.Cells(1).Resize(UBound(a, 1), UBound(a, 2))
        .Value = a
        .Sort .Cells(1, SortCol), 1, , , , , , 1
    End With
End Sub

Thats great Jindon

.Sort .Cells(1, SortCol), 1, , , , , , 1
Pl explain parameters.

 

[jindon]

        .Sort Key1:=.Cells(1, SortCol), 1, , , , , , 1

Equivalent to

        .Sort key1:=.Cells(1, SortCol), Order1:= xlAscending, Header:= xlYes

Named arguments for Sort
key1, order1, key2, type, order2, key3, order3, header,,,,
When those argument(s) is/are omitted, it should be in the order.

 

[YasserKhalil]

Thanks a lot Mr. Jindon for this perfect explanation
You are GREAT & AWESOME