Excel数据验证中的空白值下拉列表 [英] Blank Values in Excel Data Validation drop down list

问题描述

我不知道从单元格A1到A65555将出现多少个值.我有B1设置为基于A列的数据验证，如下所示A1:A65555

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

值在A列中最多显示10行.(从A1到A10)

Values are present in A column upto 10 rows. (from A1 to A10)

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

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.

注意:我已经在数据验证"设置中选中了忽略空白单元格"选项.或让我知道如何通过VBA设置数据验证?

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 ?

推荐答案

假设您在A列中没有空格，而在列底部只有一些空白行(因为数据始终为A1:AN，其中1< N< = 65555)

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)

公式=>定义名称

输入一些名称(例如ValidationList)

enter some name (like ValidationList)

使用它作为公式 = 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)

这篇关于Excel数据验证中的空白值下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！