CODEWITHSUNDEEP
excelvbaexcel-2007
logan
logan

Reputation: 8346

Blank Values in Excel Data Validation drop down list

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 ?

Upvotes: 3

Views: 25143

Answers (2)

Noam Brand
Noam Brand

Reputation: 346

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

Upvotes: 0

Ross Larson
Ross Larson

Reputation: 2437

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)

Upvotes: 4

Related Questions