How many entries can you have in List Data Validation

  • Thread starter Thread starter Rick De Marco
  • Start date Start date
R

Rick De Marco

Hi,

Does anyone know what the maximum number of entries you can have in a
data validation list? And, is there a limit of characters for each
entry?

Thanks,

Rick
 
Data validation, and other lists (ie filters) can have up
to 1000 unique entries, but only 8 will fit in the list at
a time, which makes for a lot of scrolling.

I don't know if there is a limit to the number of
characters per entry, but you'd probably want to keep it
down to less than the width of the cell you are using.
For a test, I used a list with 300 character fields, but
that's too wide to fit in the entire screen width let
alone a single cell.
 
If you are typing the list in the data validation dialog box, you're
limited to 255 characters, including the commas that separate the list
items.

If you refer to a list on the worksheet (e.g. =MyList), the limit seems
to be 32767 items.

The list will display the first 255 characters of each item, but is
limited to the width of the column. (For very narrow columns, it will
extend to about 1/2".)
 
Hi Debra,

Thanks heaps for replying to my question? When mention refering to a
list on the worksheet (e.g. =MyList), the limit seems to be 32767 items.
How do your accomplish this in vba? Currently I am just using something
like

Cells(10,10).Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="my list"

How do I get 32767 chars in my list? Do you have any other ideas how I
might solve this problem??

Thanks again,

Rick
 
You could enter the list in a column on a worksheet, e.g. Sheet2!A1:A32767

Then, select those cells, and click in the Name box, at the left of the
formula bar. Type a one word name, e.g. MyList, and press Enter.

You can refer to this list in the code: Formula1:="=MyList"
 
Hi Debra,

Thanks heaps for replying to my question? When mention refering to a
list on the worksheet (e.g. =MyList), the limit seems to be 32767 items.
How do your accomplish this in vba? Currently I am just using something
like

Cells(10,10).Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="my list"

How do I get 32767 chars in my list? Do you have any other ideas how I
might solve this problem??

Thanks again,

Rick
 
Back
Top