Excel 2010 Limitations of Sorting using custom lists

[tonyr63]

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.

1. They have field names in common
   
2. The have field names which are equivalent but different
   
3. Each list have a varying subset of the required 36 reporting fields
   
   1. List 1 has 22
      
   2. List 2 has 19
      
   3. List 3 has 17
4. They have collectively 43 different fields

What my procedures do is

1. 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
   
2. 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.

I have found if I use VBA I can get the list to sort on my custom field list and get past the 256 character limit, however I have found it does not work consistently and reliably at run time.
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:

1. 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?
   
2. Are these any other things I could try to get this to work at run time such as defined a listObject or something?
   
3. 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?
   
4. Any changes in Excel 2013 concerning the 256 character limit?

Here is the code I was using.

  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

 

[shg]

I confess to not reading the entire novel, but you can create a separate list for sort order, add a column to the table with a lookup to retrieve it, and then sort by that column.

 

[tonyr63]

H
e
l
lo


Not Clear what you - can you provide an example

 

[shg]

	A​	B​	C​	D​	E​	F​
1​	Name​	Sort​	​	Name​	Sort Pos​	​
2​	Eric	1​		Alan	7​	E2: =VLOOKUP(D2, $A$2:$B$12, 2, FALSE)
3​	Hana	2​		Barb	5​
4​	Jane	3​		Cain	8​
5​	Fran	4​		Dana	10​
6​	Barb	5​		Eric	1​
7​	Gary	6​		Fran	4​
8​	Alan	7​		Gary	6​
9​	Cain	8​		Hana	2​
10​	Kent	9​		Ivan	11​
11​	Dana	10​		Jane	3​
12​	Ivan	11​		Kent	9​

The foumula in col E looks up up the sort order in cols A & B. Sort by col E.

 

[tonyr63]

Thanks for providing suggestions.

Kind Regards


Anthony

 

[tonyr63]

Update: I found an error in my code in the long array that spans across the page making it difficult to cross check. For some reason the normal space underscore new line combination does not work with the array list. To get round this I run the sort using he Macro recorder which sorted without error however the Macro recorder produced code with syntax errors too so I was back to square one. The reason I run the recorder was to see how to break the long array and it appears the macro recorder could not do this either.


I still do not understand why I cannot pass variables defined as Ranges to the range section of the Sort function. You would expect to be able to replace:
ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(4, 1), Cells(4, cols)) – with
ActiveSheet.Sort.SortFields.Add Key:=Range(HeadRange) because the following statement is in place earlier in the Module
Set HeadRange = Range(Cells(4, 1), Cells(4, cols)).

I have a similar problem with the section .SetRange Range(Cells(4, 1), Cells(Rs, cols)) which should be replaceable with: - .SetRange Range(DataR) because this is also set as a range. I don’t get a syntax error but a runtime error when I try to use variables no matter what combination of quotes or “=HeadRange” and other I have tried.

I would prefer to not have to repeat the sorting function especially with such a long array of custom sort fields in several places but should be able to define it once and pass variables to single sorting function.
Can anyone show me how to send range variables to a sort function?

 

[RoryA]

Since HeadRange is a Range object, you don't use Range(HeadRange) but just HeadRange:

ActiveSheet.Sort.SortFields.Add Key:=HeadRange

 

[tonyr63]

Hello RoryA

I originally tried that syntax but got an error which may have been because of an issue somewhere else making me try other options.

Thanks for your assistance