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

Question

我不知道从单元格A1到A65555会有多少个值。我有B1，它是基于A列设置的数据验证，如下A1:A65555

值出现在一列中，最多10行。(从A1到A10)

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

注意:我已经在数据验证设置中勾选了“忽略空白单元格”选项。或者任何人让我知道如何通过VBA设置数据验证？

Answer 1

假设在列A中没有空格，只在列底部有一定数量的空白行(因为在数据中总是A1:AN where 1

公式=>定义名称

输入一些名称(如ValidationList)

将其用作您的公式=OFFSET(INDIRECT("Sheet1!$A$1"),0,0,COUNTA(Sheet1!$A:$A),1)

然后将数据验证列表定义为您输入的名称(并再次选中忽略空白，以便在您没有选择任何内容时不会给出错误)

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