Blank Values in Excel Data Validation drop down list

3
votes

I dont know how many values will be present from Cell A1 to A65555. I have B1 which is set with data validation based on A column as below A1:A65555

Values are present in A column upto 10 rows. (from A1 to A10)

But when i click B1, it shows empty values along with A1 to A10. I want all not null values of A column to be listed in B1.

Note: i have ticked the option "Ignore Blank cells" in Data validation setting. or Any one let me know how to set data validation through VBA ?

2
excelvbaexcel-2007
Also, the 'Ignore Blank cells' setting in the Data Valdition dialog box is used to prevent the restricted cells (those on which the Data Validation is applied, in your case B1 I guess) from giving 'Invalid Data' errors when the cells are left empty – 147

2 Answers

3
votes

assuming you will not have gaps in column A, just some amount of blank rows at bottom of column (as in data will always be A1:AN where 1 < N <= 65555)

Formulas => Define Name

enter some name (like ValidationList)

Use this as your formula =OFFSET(INDIRECT("Sheet1!$A$1"),0,0,COUNTA(Sheet1!$A:$A),1)

Then define data validation list as the the name you entered (and check ignore blanks again so it doesn't give errors when you haven't selected anything)

0
votes

I wrote some code that inserts data validation by vba programmatically and ignores blanks, so the list will be easier to scroll.

Validation in Sheets("Whatsup_msg") column J, from Source Sheets("Phones_Emails") column A. 

Sub ValidateNonBlanks()
Dim lastRowCombo As Long
Dim lastRowSource As Long
Dim rangeSource As Range
Dim rangeCombo  As Range

lastRowCombo = 150
lastRowSource = Sheets("Phones_Emails").Cells(Rows.Count, "A").End(xlUp).row

Set rangeCombo = Sheets("Whatsup_msg").Range("J4:J" & lastRowCombo)
Set rangeSource = Sheets("Phones_Emails").Range("A4:A" & lastRowSource)

With rangeCombo.Validation
    .Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Phones_Emails!$A$2:$A$" & lastRowSource
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

End Sub