String character limit to add a csv validation list via VBA
David Kingston
21
Reputation points
Is there a method for adding long comma separated variable strings to cell validation via VBA that doesn't involve listing the variables somewhere else in the workbook in a named range? I get error 1004 with any string greater than 16,382 characters.
This code works:
Dim sList As String
Dim lChar As Long
For lChar = 1 To 1638
sList = sList & "abcdefghi," '16,380 characters
Next lChar
sList = sList & "12" '16,382 characters
With Cells(1, 1)
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Formula1:=sList
End With
This code doesn't:
Dim sList As String
Dim lChar As Long
For lChar = 1 To 1638
sList = sList & "abcdefghi," '16,380 characters
Next lChar
sList = sList & "123" '16,383 characters
With Cells(1, 1)
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertWarning, Formula1:=sList
End With
Any help or advice is greatly appreciated. Thanks in advance.
DK
Sign in to answer