Как сделать из раскрывающегося списка Проверка данных исключить пробелы?

Question

Связанный: Как я могу использовать несмежные ячейки на другом листе для раскрывающегося списка Проверки данных и показывать только непустые значения?

На данный момент я обошел эту проблему, переставив лист так, чтобы все ячейки источника данных были в одном диапазоне. Я оставляю вышеупомянутый вопрос открытым, хотя, потому что я думаю, что это все еще представляет интересную проблему.

Однако теперь проблема в том, что раскрывающийся список «Проверка данных» работает не так, как я ожидал (и, как я полагаю, другие говорят мне, что он должен). Несмотря на то, что у меня все есть в одном именованном диапазоне, Excel все равно показывает пробелы в раскрывающемся списке, который ссылается на этот диапазон.

Настроить:

Лист 1

A1 = (пусто) B1 = Header
A2 = 1 B2 = Value1
A3 = 2 B3 = Value2
A4 = 3 B4 = Value3
A5 = 4 B5 = (пусто)
A6 = 5 B6 = (пусто)
A7 = 6 B7 = (пусто)

Sheet1!B2:B7 называется Validation

Sheet2!A1 настроен на использование проверки данных с источником =Validation и раскрывающимся списком в ячейке. Раскрывающийся в Sheet2!А1 показывает:

Значение1
Value2
Value3
,
,
,

(Точки представляют собой пустые строки)

Как я могу избавиться от этих пустых строк в раскрывающемся списке внутри ячейки, при этом все еще включая Sheet1!B5:B7 в источнике проверки данных?

Примечание: я обстрелял лист и попробовал его снова без столбца A из Sheet1 (поместив значения из столбца B в вышеприведенном примере в столбец A), и он работал нормально. Однако добавление столбца A вернул пробелы в раскрывающийся список «Проверка данных». Что мне нужно сделать, чтобы столбец A оставался таким, каким я его хочу, и содержать выпадающий список в ячейке чистым?

Answer 1

Вот метод, который я использую в текущем проекте.

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

Возможно, вы захотите поставить проверку для значения val > 0 , val & "" <> "" или IsNullOrEmpty(val) . IsNullOrEmpty - UDF, который я создал, так как VBA не содержит метод String, чтобы сделать это как единое целое.

Я разместил несколько фрагментов кода на сайте Microsoft Wikia . Они очень универсальны и могут использоваться практически для любой цели, как они указаны.