Public Function sorttable(ws, Table, Column1, Column2)
Dim oWorksheet As Worksheet
Dim fRangeSort As Range
Dim oRangeKey As Range
Dim oRangeKey As Range
Set oWorksheet = ws
' one range that includes all colums do sort
Set oRangeSort = oWorksheet.Range(Table)
' start of column with keys to sort
Set fRangeKey = oWorksheet.Range(Column1)
Set oRangeKey = oWorksheet.Range(Column2)
' custom sort order
Dim sCustomList(1 To 57) As String
sCustomList(1) = "Total"
sCustomList(2) = "Unallocated"
sCustomList(3) = "EE - Unallocated"
sCustomList(4) = "ACP"
sCustomList(5) = "AT"
sCustomList(6) = "AT CABINETS"
sCustomList(7) = "BC"
sCustomList(8) = "BD"
sCustomList(9) = "BW"
sCustomList(10) = "General"
sCustomList(11) = "Integrator"
sCustomList(12) = "LVS"
sCustomList(13) = "MEDIUM"
sCustomList(14) = "MES"
sCustomList(15) = "MSW"
sCustomList(16) = "PER"
sCustomList(17) = "SF"
sCustomList(18) = "TB"
sCustomList(19) = "TF"
sCustomList(20) = "US"
sCustomList(21) = "PC"
sCustomList(22) = "BOOM"
sCustomList(23) = "ME - Unallocated"
sCustomList(24) = "AU"
sCustomList(25) = "CS"
sCustomList(26) = "CH"
sCustomList(27) = "DH"
sCustomList(28) = "DR"
sCustomList(29) = "EMU"
sCustomList(30) = "EF"
sCustomList(31) = "AF"
sCustomList(32) = "OH"
sCustomList(33) = "PH"
sCustomList(34) = "WT"
sCustomList(35) = "CC - Unallocated"
sCustomList(36) = "GR"
sCustomList(37) = "CA"
sCustomList(38) = "MI"
sCustomList(39) = "EI"
sCustomList(40) = "CCD"
sCustomList(41) = "BMC"
sCustomList(42) = "CT_"
sCustomList(43) = "CM_"
sCustomList(44) = "CBL_"
sCustomList(45) = "DGN_"
sCustomList(46) = "COC_"
sCustomList(47) = "S - Unallocated"
sCustomList(48) = "S - Internal"
sCustomList(49) = "S - External"
sCustomList(50) = "Oth_"
sCustomList(51) = "NM"
sCustomList(52) = "BCP - C"
sCustomList(53) = "TB_C"
sCustomList(54) = "IS - C"
sCustomList(55) = "IS - Com"
sCustomList(56) = "IS - CS"
sCustomList(57) = "Placeholder"
Application.AddCustomList ListArray:=sCustomList
' use this if you want a list on the spreadsheet to sort by
' Application.AddCustomList ListArray:=Range("D1:D3")
oWorksheet.Sort.SortFields.Clear
oRangeSort.Sort Key1:=fRangeKey, Order1:=xlAscending, _
Key2:=oRangeKey, OrderCustom:=Application.CustomListCount + 1, _
MatchCase:=True, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
' clean up
ActiveSheet.Sort.SortFields.Clear
Erase sCustomList
Set oWorksheet = Nothing
End Function
The first and largest problem I am facing is that this function once executed results in the following excel crash after ~7-10 minutes:
Faulting application path: C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE
Faulting module path: C:\Program Files\Common Files\Microsoft Shared\Office16\mso20win32client.dll
The second problem is that the key2 sort is not occurring. I thought I set it up correctly based on the examples I could find.
Can anyone help identify why this could cause excel to crash and any ideas about how to get the double column sort to work?
