Creating an Excel list

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
323
I am creating a Validation list with VBA. For this code, it only puts the first 64 items on the list and ignores the others. Is this some type of Excel limitation I have hit? It works to reference a range on the Worksheet but I don't want to have to do that because I am creating it on the fly and don't want to have this list viewable to the user.

Columns("G:G").Select
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlEqual, Formula1:="100,101,102,110,111,150,151,152,153,154,155,159,160,161,
190,194,195,196,197,198,199,200,201,202,203,210,300,301,302,303,304,
305,306,307,308,400,401,402,403,404,405,406,407,408,409,410,411,412,
413,414,415,416,500,600,601,602,603,700,701,702,703,800,850,900,950,
1000,1001,2000,2010,2020,2030,2040,2060,2070,2080,2090,2100,2110,
3000,3010,3040,4000"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Code:
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

xapie128

Board Regular
Joined
Aug 26, 2006
Messages
82
The problem isn't the limit of 64, the problem is the Formula1 string "100,101,102,110,111,150..." is limited to 256 characters. You can test this by making the first 4 or 5 to have only single character numbers (1,2,3,4...) and see that you will now have more than 65 options in the pull-down. Creating a string variable and pointing Formula1 to it won't work either because it will still only read the first 256 characters of the string.

The best way I can think of to do it is to write your values into a far righthand column that isn't being used (hide the column or set the color to white if you don't want the numbers visible). In your example you have 82 numbers, and suppose you use column FZ, you would then use Formula1:="=$FZ$1:$FZ$82"

Clear the column before you write a new list. This scheme will require your code to have a test so it knows what the bottom cell is (FZ82 in this case).
 

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
323
Thanks for the advice. I had thought of that option, but was trying to avoid it if I could.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,538
Members
410,547
Latest member
htran4
Top