☰

如何使数据验证下拉菜单排除空白？

tag-icon • tag-icon microsoft-excel-2010 data-validation
如何使数据验证下拉菜单排除空白？

有关的： 如何在另一张工作表上使用不相邻的单元格进行数据验证下拉菜单，并仅显示非空值？

目前，我已通过重新排列工作表来解决上述问题，使所有数据验证源单元格都位于一个范围内。不过，我仍将上述问题留待解决，因为我认为它仍然是一个有趣的问题。

但是，现在的问题是“数据验证”下拉菜单没有按照我预期的方式工作（我相信其他人告诉我应该如此）。即使我已将所有内容放入一个命名范围内，Excel 仍然在引用该范围的下拉菜单中显示空白。

设置：

第 1 页

A1=（空白）B1= Header
A2= 1B2= Value1
A3= 2B3= Value2
A4= 3B4= Value3
A5= 4B5=（空）
A6= 5B6=（空）
A7= 6B7=（空）

Sheet1!B2:B7被命名Validation

Sheet2!A1设置为使用带有源的数据验证=Validation和单元格内下拉列表。下拉列表Sheet2!A1显示：

值1 值
2
值 3
...

（点代表空行）

如何才能删除单元格下拉菜单中的这些空行，同时仍将其包含Sheet1!B5:B7在数据验证源中？

A注意：我删除了工作表，然后再次尝试不添加列（将上例中Sheet1列的值放入列中），结果一切正常。但是，添加列 A 后，数据验证下拉列表中又出现了空白。我需要做什么才能让列保持我想要的样子BAA和保持单元格内下拉菜单清洁吗？

答案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

您可能需要检查 Valueval > 0或。 是我创建的 UDF val & "" <> ""，因为 VBA 不包含执行此操作的 String 方法。IsNullOrEmpty(val)IsNullOrEmpty

我已在以下网址发布了几个代码片段微软维基百科站点。它们非常通用，可以用于几乎任何目的，因为它们是指定的。

相关内容