Modify Custom Sort List Character Limit

**Michael1776** · 02-18-2012, 12:20 AM

I'm trying to create a custom sorting list in Excel 2010, but the character limit is preventing me from doing so. How can I circumvent the character limit? I need to enter a list that is more than 255 characters.

This is the warning: "The maximum length for a custom list has been exceeded. Only the first 255 characters will be saved."

**smuzoen** · 02-18-2012, 02:17 AM

You could try placing the custom list within an array and use vba to do the sort - so the custom list will be in an array - the format for using vba to do a sort with a custom list is

Please Login or Register  to view this content.

You will need to test this as I have not verified the sort works however I get no errors when the custom list is > 255 characters

**Michael1776** · 02-18-2012, 04:30 AM

Originally Posted by smuzoen

You could try placing the custom list within an array and use vba to do the sort - so the custom list will be in an array - the format for using vba to do a sort with a custom list is

Please Login or Register  to view this content.

You will need to test this as I have not verified the sort works however I get no errors when the custom list is > 255 characters

The last time I took an Excel course was when I was a freshman in college, so I apologize for not knowing how to create an array. Could you please go into more detail?

Here's an example of my custom list:

"Chinese, French, German, English, Honor, Simplified, Applied, Ace, True, Context, False"

Thank you.

**smuzoen** · 02-18-2012, 09:37 AM

My theory to use an array does not work. However if you use VBA to do a sort based on a custom list you can use a list > 255 characters. I have put together a sample workbook as proof of concept.
I have placed a list into an array which is not necessary to do this sort - I only did it to verify the custom list > 255 characters. It also demonstrates how to create an array. If you place the entire custom list into a Custom List VBA sort then you can use a list > 255 characters - you just cannot do it via the excel interface of creating the custom list.

Please Login or Register  to view this content.

Just for your information the array created above only has one item in the array - namely testStr(0)
To create a one dimensional array with multiple items in the array it is a little different - again this is just to answer your question about creating arrays however as I say creating an array is NOT required to do a sort with a Custom List. The following code is for your edification only

Please Login or Register  to view this content.

In the VB editor in the Immediate window you will see the output showing the values of each item in this one dimensional array.

**PALMWOOD** · 02-29-2012, 09:37 PM

Hi smuzoen

I have a similiar problem and want to sort all the data in my column D into this order:

HS 00, HS 01, HS 02, HS 03, HS 04, HS 05, HS 06, HS 07, HS 08, BSE 01, BSW 01, BSE 02, BSW 02, BSE 03, BSW 03, BSE 04, BSW 04, BSE 05, BSW 05, BSE 06, BSW 06, BSE 07, BSW 07, BSE 08, BSW 08, BSE 09, BSW 09, BSE 10, BSW 10, BSE 11, BSW 11, BSE 12, BSW 12, HNE 01, HNW 01, HNE 02, HNW 02, HNE 03, HNW 03, HNE 04, HNW 04, HNE 05, HNW 05, HNE 06, HNW 06, HNE 07, HNW 07, HNE 08, HNW 08, HNE 09, HNW 09, HNE 10, HNW 10, HNE 11, HNW 11, HNE 12, HNW 12, HNE 13, HNW 13, HNE 14, HNW 14, HFN 01, HFN 02, HFN 03, HFN 04, HFN 05, HFN 06, HFN 07, HFN 08, BNE 22, BNW 22, BNE 21, BNW 21, BNE 20, BNW 20, BNE 19, BNW 19, BNE 18, BNW 18, BNE 17, BNW 17, BNE 16, BNW 16, BNE 15, BNW 15, BNE 14, BNW 14, BNE 13, BNW 13, BNE 12, BNW 12, BNE 11, BNW 11, BNE 10, BNW 10, BNE 09, BNW 09, BNE 08, BNW 08, BNE 07, BNW 07, BNE 06, BNW 06, BNE 05, BNW 05, BNE 04, BNW 04, BNE 03, BNW 03, BNE 02, BNW 02, BNE 01, BNW 01, BME 12, BMW 12, BME 11, BMW 11, BME 10, BMW 10, BME 09, BMW 09, BME 08, BMW 08, BME 07, BMW 07, BME 06, BMW 06, BME 05, BMW 05, BME 04, BMW 04, BME 03, BMW 03, BME 02, BMW 02, BME 01, BMW 01, SA 01, SB 01, SA 02, SB 02, SA 03, SB 03, SA 04, SB 04, SA 05, SB 05, SA 06, SB 06, SA 07, SB 07, SA 08, SB 08, SA 09, SB 09, SA 10, SB 10, SC 10, SD 10, SC 09, SB 09, SC 08, SD 08, SC 07, SD 07, SC 06, SD 06, SC 05, SD 05, SC 04, SD 04, SC 03, SD 03, SC 02, SD 02, SC 01, SD 01

data in column D could be e.g. SC 03 - 21/1/11 or BNE 05 po lhs or even HFN 05 and 03. now i would like to record a macro that will sort my rows according to the list above so HFN 05 then BNE 05 then SC 03. Does that make sense?
I have tried to do it as a custom list first and then in a macro but the limit of 255 seems to be occuring even if i put into my macro

i am not a specialist at all so would appreciate any ideas

thanks heaps

**smuzoen** · 02-29-2012, 09:56 PM

Even though this is a similar problem could you please start this as a new thread/post. As this is your first post you may be unaware of our forum rules - one of the rules is:
Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.
You can see our forum rules from the button link at the top of this page or go to http://www.excelforum.com/forum-rule...rum-rules.html
Just copy your post above and start a new thread and if you want make reference to this thread as being of a similar type problem. Make sure the title for your new thread clearly describes your problem. To get the best chance of an answer please upload a dummy/sample workbook with no sensitive/private data demonstrating how your data is currently presented in the workbook and how you want it to appear after the solution is applied to your problem. So a before and after representation in the dummy workbook will help provide a suitable answer. To upload a sample workbook select Go Advanced, Manage Attachment and select the dummy workbook.
Thank you for your co-operation.

**Michael1776** · 03-20-2012, 07:33 PM

Thank you. My problem was solved.

**wiiwonwon** · 08-23-2022, 02:51 AM

I have a list of 582 items and 5191 characters, the custom list array seems to have a character limit of about 981. Is there a workaround for an extra long custom list?

**AliGW** · 08-23-2022, 02:55 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread