Multi-column sort with VBA

excel, sorting, vba

Solution

Try splitting your sort into 3 seperate steps, with only the second one using your custom sort order, i.e.

.Range(.Cells(1, 1), .Cells(lastrow, lastCol)).Sort _
        Key1:=.Range(.Cells(2, 4), .Cells(lastrow, lastCol)), Order1:=xlDescending, _
        MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes

.Range(.Cells(1, 1), .Cells(lastrow, lastCol)).Sort _
        Key1:=.Range(.Cells(2, 3), .Cells(lastrow, lastCol)), Order1:=xlDescending, _
        OrderCustom:=n, _
        MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes

.Range(.Cells(1, 1), .Cells(lastrow, lastCol)).Sort _
        Key1:=.Range(.Cells(2, 5), .Cells(lastrow, lastCol)), Order1:=xlAscending, _
        MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes

Note that I have reversed the order in which these sorts are performed in comparison with how they are declared in the original statement.

Problem

I'm using VBA to sort columns in Excel 2003. I need to sort by column 5 ascending, then column 3 using a custom order, then by column 4 ascending. I'm having difficulty getting the sort to work and I don't totally understand how OrderCustom applies. Any pointers in the right direction would be appreciated :) My code is below. ``` With wsData lastrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1 lastCol = .Cells(4, Columns.Count).End(xlToLeft).Column Dim n As Long Application.AddCustomList ListArray:=Array("LOW", "MEDIUM OR HIGH", "HIGH ONLY") n = Application.GetCustomListNum(Array("LOW", "MEDIUM OR HIGH", "HIGH ONLY")) + 1 Dim strSortOrder As String .Range(.Cells(1, 1), .Cells(lastrow, lastCol)).Sort _ Key1:=.Range(.Cells(2, 5), .Cells(lastrow, lastCol)), Order1:=xlAscending, _ Key2:=.Range(.Cells(2, 3), .Cells(lastrow, lastCol)), Order2:=xlDescending, _ Key3:=.Range(.Cells(2, 4), .Cells(lastrow, lastCol)), Order3:=xlDescending, _ OrderCustom:=n, _ MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes End With ```

Original source