Como faço para que um drop-down de validação de dados exclua espaços em branco?

Question

Relacionados: Como posso usar células não adjacentes em outra planilha para um menu suspenso Validação de dados e mostrar apenas espaços em branco não valores?

Por enquanto, resolvi o problema acima reorganizando minha planilha para que todas as células da Fonte de validação de dados estivessem em um intervalo. Eu estou deixando a questão acima aberta, porque eu acho que ainda representa um problema interessante.

No entanto, a questão agora é que o menu suspenso Validação de dados não está funcionando da maneira que eu esperava (e como acredito que os outros estão me dizendo que deveria). Mesmo que eu tenha tudo em um intervalo nomeado, o Excel ainda mostra espaços em branco em um menu suspenso que referencia esse intervalo.

Configuração:

Folha 1

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

Sheet1!B2:B7 é denominado Validation

Sheet2!A1 está definido para usar a validação de dados com uma origem =Validation e uma lista suspensa na célula. A lista suspensa em Sheet2!A1 mostra:

Value1
Value2
Value3
.
.
.

(os pontos representam linhas em branco)

Como posso me livrar dessas linhas em branco na lista suspensa na célula, e ainda incluir Sheet1!B5:B7 na fonte de validação de dados?

Nota: Eu nuked a folha, e tentei novamente sem coluna A de Sheet1 (colocando valores da coluna B no exemplo acima na coluna A ), e funcionou bem. Adicionando a coluna A de volta, trouxe os espaços em branco de volta para a lista suspensa Validação de dados. O que preciso fazer para manter a coluna A como eu quero e manter a lista suspensa na célula limpa?

    

Answer 1

Aqui está um método que eu uso em um projeto atual.

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

Provavelmente, você desejará verificar o valor val > 0 , val & "" <> "" ou IsNullOrEmpty(val) . IsNullOrEmpty é uma UDF que eu criei já que o VBA não contém um método String para fazer isso como um.

Eu publiquei vários snippets de código no site Microsoft Wikia . Eles são muito genéricos e podem ser usados para qualquer finalidade, como eles são especificados.