XL - Custom List character limit?

[lameid]

So I am looking at fixing some old code written by an unknown author from who knows when... It applies a custom sort.

There is this suspect looking comment to me and it is the truncation causing me problems (truncated items yield duplicates)...

Any experts on here have an opinion if this is necessary or it was with version X but good starting with version Y thoughts?

Below is a snippet of what is being done...

'Excel has a custom list length limit -- roughly 1600 characters, hence the truncation
   Application.AddCustomList ListArray:=sCustomList 'sCustomList is built by looping and truncating elements in a range based on a truncated element size of 1600/n
   
   oWorksheet.Sort.SortFields.clear
   
   oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Some of my findings...

Custom lists are stored in the registry...

http://www.techrepublic.com/blog/microsoft-office/how-do-i-create-a-custom-sort-in-excel/

Size Limits of registry elements...

http://msdn.microsoft.com/en-us/library/windows/desktop/ms724872(v=vs.85).aspx

<

http://msdn.microsoft.com/en-us/library/windows/desktop/ms724872(v=vs.85).aspx>

I just have another priority come up to keep from digging so if anyone knows and can save me some hassle it would be much appreciated.

 

[lameid]

Two Steps back the custom list worked originally but for an undesirable circumstance. Originally it was used to sort the long text like in AN that was previously truncated to shorter text, likely in in column AP but this lead to issues when the first several characters of cases were the same and lead to duplicates in the truncated versions... When I killed the truncation it threw an error. Since that didn't work I switched to a numeric range and added as AQ... Now I can get the custom sort to run in a test proc but not in the desired procedure... I've verified the ranges debugging by selecting them and viewing.... Makes me wonder if I pass all three ranges to a new procedure byval and perform the sort within if it will just work.

As for text... Not sure if you are saying a sort has to use text for custom sorts or not if using a range. Likely does for custom list but my range sort works sometimes.

Anyways it will be a while before I revisit and throw in the kitchen sink... probably yet today but not now.

 

[lameid]

Forgot to mention that the range custom sort also errored with long text... That's when I really switched to adding the coded numeric column....

 

[SkipVought]

When I attempt to load NUMBERS as NUMBERS, either manually (I get an error message "Cells without simple text were ignored" and no list was added) or via code ("Application-defined or object-defined error" and no list was added)

Excel 2007.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[lameid]

What I am reading is you are using a CUSTOM LIST still for the custom sort and it is failing creating the custom list using numbers as numbers...

Where as I am using a RANGE for the custom sort now... Although in theory a custom list should work but if I have to recode my list anyway, my thinking is I might as well stick with numbers and use the range if that will work - and it does if the code is run separately from the procedure. This is why I'm thinking there is something corrupt but not sure if Excel stores compiled code like Access or how to kill it if it does.

 

[SkipVought]

Where as I am using a RANGE for the custom sort now

Well when I manually sort Data > Sort & Filter > Sort... and in the dialog that pops up, the ONLY selection in ORDER are the Custom Lists and no option for a user defined range???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[lameid]

Sure via Excel UI you can't do it... my Sub Test (above somewhere) does run and does order the data as desired for numbers - which by everything intuitive to me is the same as what is happening in my proc.

 

[lameid]

I found a way to replicate the break...

I'm guessing there is something that Excel doesn't like by making the range in the relative fashion with the offsets... My real issue is that Transcell is set by a calling procedure... I think I can fix it by getting at the column and row of the sheet by activating the cell and pulling the column and row... but I'm open to better ideas.

Sub Test2()
   Dim oWorksheet As Worksheet
   Dim oRangeKey As Range, oRangeSort As Range, oRangeOrder As Range
   Dim TransCell As Range
   Dim i As Integer, Gcols As Integer
   
   i = 1
   Gcols = 33
   
   Set oWorksheet = Worksheets("ChartData for TARGET")
   [green]'Set oRangeSort = oWorksheet.Range("AN5:AQ37")
   'Set oRangeKey = oWorksheet.Range("AN6:AQ37")[/green]
   Set TransCell = oWorksheet.Cells(5, 39)
   Set oRangeSort = Range(TransCell.Offset(0, 2 * i + 1), TransCell.Offset(Gcols - 1, 2 * i + 2)) 'define range to be sorted
   Set oRangeKey = Range(TransCell.Offset(1, 2 * i + 2), TransCell.Offset(Gcols - 1, 2 * i + 2)) ' define key variable (Column to sort on)
   
   Set oRangeOrder = Worksheets("IndexOrderSort").Range("A1:A32")
   
   oWorksheet.Sort.SortFields.Clear
   oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=oRangeOrder, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub