¿Cómo puedo hacer que una lista desplegable Validación de datos excluya espacios en blanco?

Relacionado: ¿Cómo puedo usar células no adyacentes en otra hoja para una lista desplegable Validación de datos y sólo mostrar valores no en blanco?

Por ahora, he trabajado alrededor del problema antedicho reorganizando mi hoja así que todas las células del origen de la validación de datos están en un rango. Sin embargo, estoy dejando abierta la pregunta anterior, porque creo que todavía plantea un problema interesante.

Sin embargo, el problema ahora es que el menú desplegable Validación de datos no funciona de la forma en que esperaba (y cómo creo que otros me dicen que debería hacerlo). A pesar de que tengo todo en un rango nombrado, Excel sigue mostrando espacios en blanco en un menú desplegable que hace referencia a ese rango.

Preparar:

Hoja 1

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

Sheet1!B2:B7 se llama Validation

Sheet2!A1 está configurado para utilizar Validación de datos con una lista desplegable Origen =Validation y en la celda. La lista desplegable de Sheet2!A1 muestra:

Valor1
Valor2
Valor3
.
.
.

(Los puntos representan líneas en blanco)

¿Cómo puedo deshacerme de estas líneas en blanco en el menú desplegable de la celda, mientras Sheet1!B5:B7 en el origen de validación de datos?

Nota: Nuked la hoja, y lo intenté de nuevo sin la columna A de Sheet1 (poner valores de la columna B en el ejemplo anterior en la columna A ), y funcionó bien. Sin embargo, la adición de la columna A devolvió los espacios en blanco al menú desplegable Validación de datos. ¿Qué debo hacer para mantener la columna A mi gusto y mantener limpio el menú desplegable en la celda?

Este es un método que utilizo en un proyecto actual.

 '@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 

Probablemente querrá poner un cheque para un Value val > 0 , val & "" <> "" o IsNullOrEmpty(val) . IsNullOrEmpty es un UDF que IsNullOrEmpty creado ya que VBA no contiene un método String para hacer esto como uno.

He publicado varios fragmentos de código en el sitio de Microsoft Wikia . Son muy genéricos y se pueden utilizar para casi cualquier propósito, ya que se especifican.