Hello
I discovered to my surprise there is a limit of 256 characters set on the length of a custom list in Excel 2010. I am trying to combine 3 lists which are outputs from 3 different defect management systems that have the following characteristics.
The remarkable thing, following extensive testing, it does not fail in a consistent way either.
What I have noticed is once you have run the Macro you for that point forward run the sort manually and your list of far more than 256 characters is there for the choosing and it always sorts correctly when run manually which is different from run time behaviour?
Most of the time it sorts most of the fields correctly but getting the order wrong in different ways each time with occasional complete and correct sorts.
I’ve played around with various settings like .Header = xlYes and No with no success.
Does anyone know what I can do to troubleshoot and resolve?
Has anyone else seen this behaviour that looks a lot like a significant bug as accurately sorting is a core feature of Excel and not something you would expect to be hit and miss.
My questions are:
I discovered to my surprise there is a limit of 256 characters set on the length of a custom list in Excel 2010. I am trying to combine 3 lists which are outputs from 3 different defect management systems that have the following characteristics.
- They have field names in common
- The have field names which are equivalent but different
- Each list have a varying subset of the required 36 reporting fields
- List 1 has 22
- List 2 has 19
- List 3 has 17
- List 1 has 22
- They have collectively 43 different fields
- Cleans up the largest list re-naming some fields and deleting others and recording the list in the reporting order sorting the columns and adding missing fields as new columns
- Similar procedures work on the other lists preparing them to be appended to a single complete list for filtering and charting. Each of these lists have their headings checked against an array of required fields and new columns and field names added as requested and sorted into the order of list 1 ready to be joined up.
The remarkable thing, following extensive testing, it does not fail in a consistent way either.
What I have noticed is once you have run the Macro you for that point forward run the sort manually and your list of far more than 256 characters is there for the choosing and it always sorts correctly when run manually which is different from run time behaviour?
Most of the time it sorts most of the fields correctly but getting the order wrong in different ways each time with occasional complete and correct sorts.
I’ve played around with various settings like .Header = xlYes and No with no success.
Does anyone know what I can do to troubleshoot and resolve?
Has anyone else seen this behaviour that looks a lot like a significant bug as accurately sorting is a core feature of Excel and not something you would expect to be hit and miss.
My questions are:
- What is the syntax required to pass variable dimensioned as a Range to both Key:=Range and the .SetRange parameter as I could not get this to work and used (Cells(4, 1), Cells(4, cols)) instead?
- Are these any other things I could try to get this to work at run time such as defined a listObject or something?
- I notices that sometimes the custom list would seem to appear twice in the custom lists section in advanced options so is there a way to stop it been injected into this list more than once. Is there code to check if the custom list already exists?
- Any changes in Excel 2013 concerning the 256 character limit?
Code:
Sub ReOrder()
Dim DataR As Range, r As Range, HeadRange As Range, cols As Long, Rs As Long, i As Long
ThisWorkbook.ActiveSheet.Select
Set r = ActiveSheet.UsedRange
Rs = r.Rows.Count - 1
cols = r.Columns.Count
Set DataR = Range(Cells(4, 1), Cells(Rs, cols))
Set HeadRange = Range(Cells(4, 1), Cells(4, cols))
ActiveSheet.Sort.SortFields.Clear
Application.AddCustomList listarray:=Array("Unique ID", "Short Description", _
"Ship Number", "Reporting Source", "Work Order Number", "Date Identified", _
"Assignee", "Need Date", "Expected Rectification Date", "Sequencing Priority", _
"Impact Priority", "System Name", "Configuration Item", "Equipment Name", "Equipment Part/Stock Number", _
"Equipment Serial Number", "Assembly Name", "Part Number", "Part Serial Number", "MRP Catalogue Number", _
"Activity", "Location", "Reference", "Department", "Validator", "Detailed Defect Description", "Status", _
"Updated", "Labels", "Assigned To External Process", "Notify of Creation", "Test Number", "Delivery Certificate Comment", _
"Responsible Party", "Responsibility Category", "Effect on Capability")
ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(4, 1), Cells(4, cols)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Unique ID, Short Description, Ship Number, Reporting Source, Work Order Number, Date Identified, Assignee, Need Date, Expected Rectification Date, Sequencing Priority, Impact Priority, System Name, Configuration Item, Equipment Name, Equipment Part / Stock Number, Equipment Serial Number, Assembly Name, Part Number, Part Serial Number, MRP Catelogue Number, Activity, Location, Reference, Department, Validator, Detailed Defect Description, Status, Updated, Labels, Assigned To External Process, Notify of Creation, Test Number, Effect on Capability, Responsible Party, Responsibility Category, Department", DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(Cells(4, 1), Cells(Rs, cols))
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
End Sub
Last edited by a moderator:
