how to ignore blank cells from the data validation list?

Hi ,

Please refer the below link

http://www.cpearson.com/excel/NoBlanks.aspx

Rajul,

that link does not answer my question.   please see my question in the attachment file i uploaded in my original post.

Hi Flora,

First you need to create a separate list in another column, I selected H in attached added below formula:

=IFERROR(INDEX($A$2:$A$100,SMALL(IF(ISTEXT($A$2:$A$100),ROW($A$1:$A$99),""), ROW(A1))),"")

Select allOpen in new window

Then Create a New NameRange, I created Names with below formula:

=OFFSET(Data!$H$2,0,0,COUNTIF(Data!$H$2:$H$100,">"""))

Select allOpen in new window

Then in your Data Validation List =Names
Please find attached for your reference.
Hope this helps
Flora_Data-Validation-Without-Blank.xlsx

Shams, thanks. can this be done without use of helper column?

Hi Flora,

Regrettably no

@Flora
You can use VBA to get the desired drop down list.
Place the below code on Data Sheet Module and it will insert a drop down validation in column D Row2 downward when a cell is selected.

See if this is something you can work with.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim ws As Worksheet
Dim tbl As ListObject
Dim dict, x
Dim i As Long

If Target.Column = 4 And Target.Row > 1 Then
    Set ws = Sheets("Data")
    Set tbl = ws.ListObjects(1)
    x = tbl.DataBodyRange.Columns(1).Value
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = 1 To UBound(x, 1)
        dict.Item(x(i, 1)) = ""
    Next i
    
    On Error Resume Next
    With Target.Validation
        .Delete
        .Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Formula1:=Join(dict.keys, ",")            
    End With
End If
End Sub

Select allOpen in new window

DropDownList.xlsm

Shums pointed out a bug in the proposed code that in the drop down list a single name is changed into two items because the lastname and firstname are separated by a comma. Thanks Shums!

Here is a workaround, place both the code on Data Sheet Module and the drop down list will show the names without a comma separating the lastname and the first name but when you pick a name from the drop down, lastname and the firstname will be separated by a comma again.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column = 4 And Target.Row > 1 Then
    If Target <> "" Then
        Application.EnableEvents = False
        Target = WorksheetFunction.Substitute(Target.Value, " ", ", ", 1)
        Application.EnableEvents = True
    End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim ws As Worksheet
Dim tbl As ListObject
Dim dict, x
Dim i As Long

If Target.Column = 4 And Target.Row > 1 Then
    Set ws = Sheets("Data")
    Set tbl = ws.ListObjects(1)
    x = tbl.DataBodyRange.Columns(1).Value
    Set dict = CreateObject("Scripting.Dictionary")
    
    For i = 1 To UBound(x, 1)
        dict.Item(Replace(x(i, 1), ",", "")) = ""
    Next i
    
    On Error Resume Next
    With Target.Validation
        .Delete
        .Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Formula1:=Join(dict.keys, ",")            
    End With
End If
End Sub

Select allOpen in new window

Thanks Neeraj for coming up with VBA approach, it works perfect now.

Thanks for confirming Shums!
Hope it would work for Flora as well.

thank you Subodh Tiwari.

i paste the code in Data sheet and it works just fine until you save and close the workbook and once you reopen the workbook. the workbook crashes and everything is lost.

the code makes the workbook corrupt.

you can try it on my file Flora_Data-Validation-Without-Blank.xlsx

Please find the corrected workbook.
DropDownListNewFile.xlsm

Thanks.

the macro works.

untick Remove personal information was the culprit. i unmarked it and it worked.

You're welcome Flora! Glad it worked.