Sort by multiple criteria excel vba-CodePudding

Simple spreadsheet, would like to sort by two criteria. My code works when I specify cells, i.e., A2:A21, but not when I substitute variable name, i.e., "Prioritycolumn". This code moves priority rows to the top, but doesn't sort by value. Sorts by OMColumn properly. EDIT: Even when I remove the second sort, returns all priority rows at the top, but not in ascending order.

Application.Goto Reference:="ListArea"
ActiveWorkbook.Worksheets("PARTS ORDER LIST").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("PARTS ORDER LIST").Sort.SortFields.Add2 Key:=Range _
    (PriorityColumn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal

ActiveWorkbook.Worksheets("PARTS ORDER LIST").Sort.SortFields.Add2 Key:=Range _
    (OMColumn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    
With ActiveWorkbook.Worksheets("PARTS ORDER LIST").Sort
    .SetRange Range(AllColumn)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

CodePudding user response：

Following up my comment, I believe this would give you a better result:

With ActiveWorkbook.Worksheets("PARTS ORDER LIST")
    .Range(.Cells(1,1),.Cells(21,lastCol)).Sort Key1:=.Range(.Cells(1,PriorityColumn),.Cells(21,PriorityColumn)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal, Header:=xlyes
    .Range(.Cells(1,1),.Cells(21,lastCol)).Sort Key1:=.Range(.Cells(1,OMColumn),.Cells(21,OMColumn)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal, Header:=xlyes
End With

This sort PriorityColumn first (lowest priority), followed by sorting OMColumn (highest priority).

Note that lastCol is used as I don't know the last column in your total range.

CodePudding user response：

Found the error. There were some numbers entered as text. Code works as originally printed. Thanks for your help!