maximum rows custom list

J

Jootje

Hi,

I have about 2000 rows I want to add in a custom list in order to sort
another column that contains 4000 rows. The problem is that the custom list
only uses the first 180 rows or so. It seems like the maximum rows or items
you can add to a custom list is 180. Does anybody know a solution to this
problem.?

p.s. already split the 2000 rows in 180 rows parts...this is tricky
however...you tend to make mistakes...

Please help,
thanks in advance,
Jootje
 
N

Norman Jones

Hi Jootie,

A long custom list can be added programmatically.

Try:

'======================>>
Public Sub Tester001()

Dim rng As Range
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ActiveWorkbook '<<========== CHANGE
Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
Set rng = SH.Range("A1:A2000") '<<========== CHANGE

Application.AddCustomList ListArray:=rng

End Sub
'<<======================

Change the workbook and worksheet names to suit and change the address of
rng to accord with your list.

If you are not familiar with macros, you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
D

Debra Dalgleish

The custom list limit is about 254 items, or 1818 characters, whichever
it hits first. AFAIK, there's no way to change the limit, manually or
programmatically.
 
N

Norman Jones

Hi Debra,

You are correct.

I had used the custom list feature to hold an alphanumeric parts list of
several thousand items. As the list comprised an uninterrupted sequence,
Excel's autofill ensured that dragging an initial part number provided a
complete list, and disguised (from me) the limits that you refer to.

Thank you for the correction!
 
J

Jootje

Hi Debra and Norman,

Thanks anyway for your help, I'll stick to my old plan then.

Regards,
Jootje
 
Top