How to customize list behave of data validation

[6656]

If I want the list behavior of data validation having same function as a Combox, which filters values with distinct, and non_blank in order.

Note: A cell dropdown_list set by using menu bar DATA --> validation --> List........... or a macro module as:

Sub LISTBOX()
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$E$1:$E$20"
.ShowInput = True
.ShowError = True
End With
End Sub

Thanks.

 

[xlbo]

Only way to do that would be to iterate thru the list , creating a comma seperated string of list items, which you would then use as the validation list
There is no set of options for data validation to do what you want Rgds
~Geoff~

 

[6656]

Thanks Rgds for your commnet.