CODEWITHSUNDEEP
excelvbasortingautofiltercustom-lists
Chipperzs
Chipperzs

Reputation: 21

VBA for AutoFilter Sort SortFields not sorting with additional column sorts

I have an array of data several rows down from the top. I have working VBA code that bounds the data with an Range and stores it in a variable. Then resizes to include a header row, clears the filter and then adds it back per the selected range.

At that point I want to sort the data per 3 rows

I use Cells.Find to find the column title I want and then sort the row and column into variables. I add 1 to the Column variable and store that as a variable for the next sort...

I've tested each of the sort commands and they work independently but I'm not able to get them to work in a combined order Date:, then Priority: then Status:.

Thanks for the help in advance.

Cells.Find(What:="Due:", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

Dim DueRowNumber As Long
Dim DueColumnNumber As Long

DueRowNumber = ActiveCell.Row
DueColumnNumber = ActiveCell.Column
   
Dim PriorityRowNumber As Long
Dim PriorityColumnNumber As Long

PriorityRowNumber = ActiveCell.Row
PriorityColumnNumber = ActiveCell.Column + 1

Dim StatusRowNumber As Long
Dim StatusColumnNumber As Long

StatusRowNumber = ActiveCell.Row
StatusColumnNumber = ActiveCell.Column + 2


ActiveWorkbook.Worksheets("Action Items").AutoFilter.Sort.SortFields.Add Key _
    :=Cells(DueRowNumber, DueColumnNumber), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortTextAsNumbers
ActiveWorkbook.Worksheets("Action Items").AutoFilter.Sort.SortFields.Add Key _
    :=Cells(PriorityRowNumber, PriorityColumnNumber), SortOn:=xlSortOnValues, Order:=xlAscending, _
    CustomOrder:="High,High-Med,Med,Med-Low,Low", _
    DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Action Items").AutoFilter.Sort.SortFields.Add Key _
    :=Cells(StatusRowNumber, StatusColumnNumber), SortOn:=xlSortOnValues, Order:=xlDescending, _
    CustomOrder:="Late,In Work,Not Started,On Hold,Completed", _
    DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Action Items").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Upvotes: 0

Views: 702

Answers (1)

Chipperzs
Chipperzs

Reputation: 21

After further investigation with additional data (added several more row with unique Due Dates, Priorities, and Status settings... the above code appears to work. I hope it helps someone with a similar issue.

Upvotes: 0

Related Questions