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.

Related Question