Forum Discussion
Maverick494
Jun 19, 2020Copper Contributor
Sort function causes a crash and does not perform secondary sort
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?
No RepliesBe the first to reply