VBA - Maximum character in dropdown-list

Question

' Validação Motivo
Columns(motivo).Select
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "TESTE1;TESTE2;TESTE3;TESTE4;TESTE5;TESTE6;TESTE7;TESTE8;TESTE9;TESTE10;TESTE11;TESTE12;TESTE13;TESTE14;TESTE15;TESTE16;TESTE17;TESTE18;TESTE19;TESTE20;TESTE21;TESTE22;TESTE23;TESTE24;TESTE25;TESTE26;TESTE27;TESTE28;TESTE29;TESTE30;TESTE31;TESTE32;TESTE33"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

If my 'test' list exceeds 256 characters, my list is blocked and if it exceeds 1024, an error is received.

In the above example, I have a list up to teste33, totaling 256 characters. How I would add the teste34 and so, get around this error?

Answer 1

Referring to this Global OS reply: Maximum drop-down list/formula length in Excel

The step-by-step will be explained below:

Create Spreadsheet with Validation Data

First a spreadsheet should be created with the validation data and not the mode:

Formula1:= _ "TESTE1;TESTE2;TESTE3;TESTE4;TESTE5;TESTE6;TESTE7;TESTE8;TESTE9;TESTE10;TESTE11;TESTE12;TESTE13;TESTE14;TESTE15;TESTE16;TESTE17;TESTE18;TESTE19;TESTE20;TESTE21;TESTE22;TESTE23;TESTE24;TESTE25;TESTE26;TESTE27;TESTE28;TESTE29;TESTE30;TESTE31;TESTE32;TESTE33"

The Name Sheet ListaValid should be created and the validation data placed in some column, in the example in Column A, as in the image:

Code

The code below inserts the Column A list of the Listavalid spreadsheet as validation in column C of the Worksheet.

LastRow = Worksheets("ListaValid").Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="List", RefersTo:="=ListaValid!$A$1:$A$" & LastRow
Worksheets("Planilha1").Range("C:C").Validation.Delete
Worksheets("Planilha1").Range("C:C").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List"

Note: To avoid blank cell errors in the validation list, a function to delete this data can be added.

Upshot

A test was performed with the validation list with data from TESTE1 to TESTE400, or 400 lines.

The result is as follows:

Explanation

Last line

LastRow = Worksheets("ListaValid").Cells(Rows.Count, "A").End(xlUp).Row

Find the last row of the spreadsheet from the validation list and column A, ie the data from the list must be filled from row 1, in order.

Create Name List

ActiveWorkbook.Names.Add Name:="List", RefersTo:="=ListaValid!$A$1:$A$" & LastRow

Create a List with the validation sheet data in the Range from A1 to A & Last Line

Delete Validation

Worksheets("Planilha1").Range("C:C").Validation.Delete

Delete all validation from column C to insert it into the next row and avoid errors.

Insert Validation

Worksheets("Planilha1").Range("C:C").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List"

Inserts the validation with the data from the created list.