We help IT Professionals succeed at work.

how to ignore blank cells from the data validation list?

4,670 Views
Last Modified: 2017-03-28
I have the attached workbook.

i have used the formula in the dropdown list of data validation list.

as you can see if my data has blank cells they still appear in the dropdown. how can this be modified so that it does not list the blank cells in the dropdown?
Book.xlsx
Comment
Watch Question

Rajul RajInformation Security Officer

Commented:
Hi ,

Please refer the below link

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

Author

Commented:
Rajul,

that link does not answer my question.   please see my question in the attachment file i uploaded in my original post.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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))),"")

Open 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,">"""))

Open 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
Flora EdwardsMedicine

Author

Commented:
Shams, thanks. can this be done without use of helper column?
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi Flora,

Regrettably no
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
@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

Open in new window

DropDownList.xlsm
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Open in new window

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Thanks Neeraj for coming up with VBA approach, it works perfect now.
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

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

Author

Commented:
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
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
Please find the corrected workbook.
DropDownListNewFile.xlsm
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Flora EdwardsMedicine

Author

Commented:
Thanks.

the macro works.
Flora EdwardsMedicine

Author

Commented:
untick Remove personal information was the culprit. i unmarked it and it worked.
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Flora! Glad it worked.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.