How can I get the listbox to show more than 20 lines?

[geecee]

The data list in the attached programme has 35 items but the listbox only shows 20. How can I fix it so that the listbox shows the 35 - or can't I?

This is a test programme as the list I intend to use will be ??? number of items.

Any help will be appreciated.

MYLIST_TESTING_MODEL2.xlsm

[Rudi]

Hi George,

Your initial validation rule for the drop down was only going down to A21, whereas your list was extending past that already.

SC1.jpg

In order for this to be maintained automatically it is best to convert your source list into a table (that is essentially a dynamic range name).
Then you can put the range name into the source of the validation drop down. In this particular case, it would not be possible to just use the table name, as your table had more than one column. So to overcome this, one would have to reference it in an INDIRECT function and extract specifically the column you want to reference. See the image below.

SC2.jpg

I have attached the updated workbook as well. I have modified the VLOOKUP to reference the table and I also used a formula to reference sheet 2 from sheet 3. Your macro does not make much sense (and unless I am missing the point of it, it is not really necessary?!)

MYLIST_TESTING_MODEL2.xlsm

[geecee]

Rudi

Thanks for your reply. Very much appreciated.