vba Excel 数据验证下拉列表中的空白值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11242399/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Blank Values in Excel Data Validation drop down list
提问by logan
I dont know how many values will be present from Cell A1 to A65555. I have B1 which is set with data validation based on A column as below A1:A65555
我不知道从单元格 A1 到 A65555 会有多少个值。我有 B1 设置了基于 A 列的数据验证，如下 A1:A65555
Values are present in A column upto 10 rows. (from A1 to A10)
值出现在最多 10 行的 A 列中。(从A1到A10)
But when i click B1, it shows empty values along with A1 to A10. I want all not null values of A column to be listed in B1.
但是当我单击 B1 时，它显示空值以及 A1 到 A10。我希望 A 列的所有非空值都列在 B1 中。
Note: i have ticked the option "Ignore Blank cells" in Data validation setting. or Any one let me know how to set data validation through VBA ?
注意：我在数据验证设置中勾选了“忽略空白单元格”选项。或者有人让我知道如何通过 VBA 设置数据验证？
采纳答案by Ross Larson
assuming you will not have gaps in column A, just some amount of blank rows at bottom of column (as in data will always be A1:AN where 1 < N <= 65555)
假设您在 A 列中没有间隙，只有在列底部有一些空白行（如数据将始终为 A1:AN，其中 1 < N <= 65555）
Formulas => Define Name
公式 => 定义名称
enter some name (like ValidationList)
输入一些名称（如 ValidationList）
Use this as your formula =OFFSET(INDIRECT("Sheet1!$A$1"),0,0,COUNTA(Sheet1!$A:$A),1)
将此作为您的公式 =OFFSET(INDIRECT("Sheet1!$A$1"),0,0,COUNTA(Sheet1!$A:$A),1)
Then define data validation list as the the name you entered (and check ignore blanks again so it doesn't give errors when you haven't selected anything)
然后将数据验证列表定义为您输入的名称（并再次检查忽略空白，以便在您未选择任何内容时不会出错）
