custom sort not applying

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
521
I've recorded a custom sort.
The sort works exactly how I want, when manually applied (during record process), but fails to apply when applied by executing recorded vba code; by fail, I mean not applying sort and no error messages displayed.
After the code has completed, I can visit the "Custom Sort" GUI and see my custom sort detailed in there (vba is correctly detailing my sort levels); Pressing ok from this screen, produces a sort warning to which I choose ok and then my sort actually applies.

The key piece that appears to be failing to execute, is the applying of the sort. Any idea's?

Code:
    ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Add2 Key:=Range( _
        "M2:M9808"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Add2 Key:=Range( _
        "A2:A9808"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Add2 Key:=Range( _
        "E2:E9808"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Orders").Sort
        .SetRange Range("A1:O9808")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,258
Office Version
2007
Platform
Windows
Change Add2 to Add and I added a variable to get the last row, test if it works for you.

Code:
Sub test()
  Dim lr As Long
[COLOR=#0000cd]  lr = Range("M" & Rows.Count).End(xlUp).Row[/COLOR]
  With ActiveWorkbook.Worksheets("Orders").Sort
    .SortFields.Clear
    .SortFields.[COLOR=#0000ff]Add [/COLOR]Key:=Range("M2:M" & lr), SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    .SortFields.[COLOR=#0000ff]Add [/COLOR]Key:=Range("A2:A" & lr), SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.[COLOR=#0000ff]Add [/COLOR]Key:=Range("E2:E" & lr), SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("A1:O" & lr)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,258
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,245
Messages
5,449,225
Members
405,557
Latest member
AJTatsuo

This Week's Hot Topics

Top