Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
Okay, I found a solution but really only have about half a clue how it works. Could someone take a look at this and post an answer that explains it all? youtube.com/watch?v=6PcF04bTSOM –  Iszi Oct 16 '12 at 3:59
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.