使用VBA从数据范围条件填充Excel数据验证下拉列表 [英] Populate Excel Data Validation Drop-Down From Data Range Condition Using VBA

问题描述

我正在构建一个电子表格，其中包含数据验证下拉菜单供用户选择.该下拉列表是根据一些预定义条件从(隐藏)选项卡上的命名范围填充的.

I am building a spreadsheet which contains data validation drop-downs for users to select. The dropdown is populated from a named range on a (hidden) tab based on some predefined conditions.

数据范围(当前> 500行)的提取为

The an extract of the data range (which is currently >500 rows) is

| Type | Code | Description      | Start Date | End Date   | Status |
| A    | 001  | IT               | 01/01/2016 | 31/12/2016 | O      |
| A    | 002  | HR               | 31/10/2017 | 31/12/2018 | O      |
| A    | 003  | Payrol           | 01/01/2016 | 31/12/2016 | O      |
| A    | 004  | Marketing        | 01/01/2016 | 31/12/2016 | C      |
| B    | 110  | Technical Review | 01/01/2016 | 31/12/2016 | O      |

并且在代码数据"的命名范围内

And is in a Named Range 'Code Data'

我想使用VBA在以下数据范围内的代码列中填充数据验证下拉列表:

I am wanting to populate a data validation dropdown with the code column using VBA from the data range where:

• 类型= A
• 状态= O
• 开始日期<今天的日期
• 结束日期>今天的日期

我尝试使用ODBC/SQL，该方法效果很好，但启动速度很慢–我认为它是在查询之前已将其连接到数据范围

I have attempted using ODBC/SQL which works well but is slow to start up – I assume it’s making it’s connection to the data range before querying

有更快/更好的方法吗?

Is there a faster/better way?

推荐答案

我认为数据验证需要一个连续的范围，因此您可能需要先过滤，然后将结果单元格移动到其他位置，否则数据验证将包括隐藏的单元格，就像这样(显然会修改为您自己的工作表和范围):

I think data validation needs a continuous range, so you'll probably need to filter first then move the resulting cells elsewhere, or the data validation will include the hidden cells, something like this (obviously amend to your own sheet and ranges):

Sub someMacro()
With Sheets("Sheet1")
    With .Range("a1:f6")
        .AutoFilter Field:=1, Criteria1:="A"
        .AutoFilter Field:=6, Criteria1:="O"
        .AutoFilter Field:=4, Criteria1:="<" & Date
        .AutoFilter Field:=5, Criteria1:=">" & Date
    End With
    .Range(.Range("b2"), .Range("b2").End(xlDown)).Copy
    .Range("g1").PasteSpecial xlPasteValues 'Put this whereever is suitable, don't forget to add something in to delete this with each run
    .Range("h1").Validation.Add Type:=xlValidateList, Formula1:="='Sheet1'!" & .Range(.Range("g1"), .Range("g1").End(xlDown)).Address 'I've validated cell h1, change this to whatever
End With
End Sub

这篇关于使用VBA从数据范围条件填充Excel数据验证下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！