Excel数据验证下拉列表中的空值

Question

我不知道从单元格A1到A65555会有多少个值。 我有一个B1单元格，根据以下的A列设置了数据有效性验证： A1:A65555

A列中最多有10行值（从A1到A10）。

但是当我单击B1时，它显示空值以及A1到A10。 我希望所有A列的非空值都能列在B1中。

注意：我已经在数据验证设置中勾选了“忽略空单元格”选项。 或者有人可以告诉我如何通过VBA设置数据验证？

- logan

---

此外，在数据验证对话框中的“忽略空单元格”设置用于防止受限制的单元格（在您的情况下是B1）在单元格为空时产生“无效数据”错误。 - 147

Answer 1

假设您的列A中没有空白行，只有一些在该列底部的空行（数据将始终为A1:AN，其中1< N <= 65555）。

公式 => 定义名称

输入名称（例如ValidationList）

使用此公式作为您的公式：= OFFSET（INDIRECT（“Sheet1！$ A $ 1”），0,0，COUNTA（Sheet1！$ A：$ A），1）

然后将数据验证列表定义为您输入的名称（并再次检查忽略空格，以便在未选择任何内容时不会出现错误）。

- Ross Larson

---

好奇心 - 如果在 A1:AM 中存在空单元格，需要进行哪些更改，其中 M 是最后一个填充的行？我知道如何使用 VBA 来完成它，但是否有一些非 VBA 的方法？ - 147

可能是可以做到的，但处理任意大小和数量的间隙会非常混乱。我认为对于这种情况，使用VBA会更容易。对于OP的需求，该公式非常有效。 - Ross Larson

Answer 2

我编写了一些代码通过vba程序插入数据验证，并忽略空白，使得列表更容易滚动。

在"Whatsup_msg"工作表的J列中进行验证，来源于"Phones_Emails"工作表的A列。

Sub ValidateNonBlanks()
Dim lastRowCombo As Long
Dim lastRowSource As Long
Dim rangeSource As Range
Dim rangeCombo  As Range

lastRowCombo = 150
lastRowSource = Sheets("Phones_Emails").Cells(Rows.Count, "A").End(xlUp).row

Set rangeCombo = Sheets("Whatsup_msg").Range("J4:J" & lastRowCombo)
Set rangeSource = Sheets("Phones_Emails").Range("A4:A" & lastRowSource)

With rangeCombo.Validation
    .Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Phones_Emails!$A$2:$A$" & lastRowSource
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

End Sub

- Noam Brand