Sorting...

[ErikJan]

Sorry, another one I'm looking to simplify...

Code: Select all

    Activesheet.Sort.SortFields.Clear
    Activesheet.Sort.SortFields.Add Key:=Range("C2:C57"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Activesheet.Sort.SortFields.Add Key:=Range("B2:B57"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Activesheet.Sort
        .SetRange Range("A1:O57")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Need to sort a variable range, based on cols B and C. I want that without providing the size of the array (I believe Excel can 'guess'). So something like:

Code: Select all

    Activesheet.Sort.SortFields.Clear
    Activesheet.Sort.SortFields.Add Key:=Column(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Activesheet.Sort.SortFields.Add Key:=Column(2), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Activesheet.Sort
        .SetRange Cells
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Note the Key specification and the range... I can (and will) try but could this work somehow?

[HansV]

Try

Range("A1").CurrentRegion.Sort Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("B1"), Order:=xlDescending, Header:=xlYes

[ErikJan]

My simplified example worked...

Your suggestion worked as well and that's the one I'll use. I recall that this was how I always did it in VBA2003 (also as that was the way it was 'recorded'); in my VBA2010 I recorded my examples above (and since I don't remember all from the past I tried to go with that; your solution is much nicer!)

[HansV]

The Sort object as a property of a worksheet was introduced in Excel 2007. It is more powerful than the Sort method of a range - you can sort on any number of keys instead of up to three keys. But it's more complicated to use, and it won't work for users who still have Excel 2003 or earlier, so for the moment I stick to the Sort method of a range.