Sorting by two different columns

[Mr2017]

Hi

I've got some data which I'd like to sort alphabetically in one column, then numerically in another column (after the alphabetical sort has been done). The numerical sort should be in descending order.

I've got this which sorts it alphabetically

Sub A()


    With Sheet4
    Range("d5", Range("d5").End(xlDown)).Select
    Selection.Sort Key1:=.Range("d4"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End With
End Sub

But when I run this afterwards, it only sorts the data in that column, instead of the range. Does anyone know why, please?

Sub Sort_A()
    With Sheet4
         Range("k5", Range("k5").End(xlDown)).Select
         Selection.Sort Key1:=Range("k5"), Order1:=xlDescending, Header:=xlNo
    End With


End Sub

 

[Joe4]

You have two different sort routines, that do primary sorts. So whichever one you run last will overerwrite the first.
You can sort the same range with a multi-level sort (you would have Key1, Key2, Order1, Order2, etc).

Try turning on your Macro Recorder and manually set up a multi-level sort to see what that VBA code looks like.

 

[Mr2017]

Hi Joe

I've got a Windows 10 laptop and it keeps on crashing whenever I try to record a macro, then Excel re-starts. It seems to be a common problem on google, but there are no clear instructions on how to fix it...?

 

[Joe4]

What is the ENTIRE range that you want to sort (which columns are included)?
What is the best column to look at to determine exactly where our data ends?

 

[Mr2017]

Hi Joe

I had to record the actions on a separate computer.

The code is below.

I'm going to change the sheet references, but do you know how I can incorporate the "end xl down" functionality in this? The recorder only uses absolute references (eg D5:D168), which won't work when the range of data changes. I'll always sort the data from row 5.

Sub A ()




    Sheets("Rev").Select
    Range("B4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Rev").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Rev").Sort.SortFields.Add2 Key:=Range( _
        "D5:D168"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Rev").Sort.SortFields.Add2 Key:=Range( _
        "K5:K168"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Review").Sort
        .SetRange Range("B4:R168")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[DanteAmor]

Try this:

Sub A()
    Sheets("Rev").Select
    Range("B4:R4", Range("B4").End(xlDown)).Sort key1:=Range("D5"), order1:=[COLOR=#008000]xlAscending[/COLOR], key2:=Range("K5"), order2:=[COLOR=#0000ff]xlDescending[/COLOR], Header:=xlYes
End Sub

Note: in your macro you have 2 sheets, "Rev" and "Review", but I suppose it is a typing error.

 

[Mr2017]

Yes, that's correct re the typo - thanks Dante!

The code worked!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.