Excel – How to make a Data Validation drop-down exclude blanks

data validationmicrosoft-excel-2010

Related: How can I use non-adjacent cells on another sheet for a Data Validation drop-down, and only show non-blank values?

For now, I've worked around the above problem by re-arranging my sheet so all the Data Validation Source cells are in one range. I'm leaving the above question open though, because I think it still poses an interesting problem.

However, the issue now is that the Data Validation drop-down isn't working in the way I expected it to (and how I believe others are telling me it should). Even though I've got everything into one named range, Excel still shows blanks in a drop-down that references that range.

Setup:

Sheet 1

A1= (blank) B1= Header
A2= 1 B2= Value1
A3= 2 B3= Value2
A4= 3 B4= Value3
A5= 4 B5= (empty)
A6= 5 B6= (empty)
A7= 6 B7= (empty)

Sheet1!B2:B7 is named Validation

Sheet2!A1 is set to use Data Validation with a Source =Validation, and in-cell drop-down. The drop-down in Sheet2!A1 shows:

Value1
Value2
Value3
.
.
.

(Dots represent blank lines)

How can I get rid of these blank lines in the in-cell drop-down, while still including Sheet1!B5:B7 in the Data Validation Source?

Note: I nuked the sheet, and tried it again without column A from Sheet1 (putting values from column B in the above example into column A), and it worked fine. Adding Column A back though, brought the blanks back into the Data Validation drop-down. What do I need to do to keep column A as I want it and keep the in-cell drop-down clean?

Best Answer

  • Here is a method that i use in a current project.

    '@ws - Worksheet object
    '@col - String value
    'Notes:
    '  - @ws should be the worksheet that contains the Range @col
    '  - @col should be a Column/Row based Range, should be only 1 Column or Row
    Function UniqueValues(ws As Worksheet, col As String) As Variant
    
       Dim rng As Range: Set rng = ws.Range(col)
       Dim dict As New Scripting.Dictionary
    
       If Not (rng Is Nothing) Then
          Dim cell As Range, val As String
    
          For Each cell In rng.Cells
             val = CStr(cell.Value)
    
             If InStr(1, val, ",") > 0 Then
                val = Replace(val, ",", Chr(130))
             End If
    
             If Not dict.Exists(val) Then
                dict.Add val, val
             End If
    
          Next cell
       End If
    
       'Return value as Variant Array
       UniqueValues = dict.Items
    End Function
    

    You will probably want to put in a check for a Value val > 0 ,val & "" <> "" or IsNullOrEmpty(val). IsNullOrEmpty is a UDF that i created since VBA does not contain a String method to do this as one.

    I have posted several code-snippets at Microsoft Wikia site. They are very generic and can be used for just about any purpose, as they are specified.