如何用Cells(Rows.Count,1).End(xlUp).Row动态更新Excel下拉验证列表？

问题描述（翻译自英文） #

我需要将Sheet1中A列的数据（每日获取的数据长度会变化）添加到Sheet2指定单元格（E9、E10、E11）的下拉验证列表中。当前代码使用固定范围“A2:A13”，请问如何通过Cells(Rows.Count, 1).End(xlUp).Row实现动态范围选择？同时，代码中Validation.Add xlValidateList后有三个逗号，请问其原因是什么？

当前代码如下：

'Select and update new AccessionGroups

Sub Update()

Dim WS As Worksheet
Dim WS2 As Worksheet

Set WS = Sheet1
Set WS2 = Sheet2

'lastrow = Worksheets(WS).Cells(Rows.Count, 1).End(xlUp).Row (**Trying to figure out how to use this)

WS2.Range("E9").Validation.Delete
WS2.Range("E9").Validation.Add xlValidateList, , ,"='" & WS.Name & "'!" & WS.Range("A2:A13").Address
     
WS2.Range("E10").Validation.Delete
WS2.Range("E10").Validation.Add xlValidateList, , ,"='" & WS.Name & "'!" & WS.Range("A2:A13").Address

WS2.Range("E11").Validation.Delete
WS2.Range("E11").Validation.Add xlValidateList, , ,"='" & WS.Name & "'!" & WS.Range("A2:A13").Address

End Sub

1. 实现动态范围选择的方法 #

首先修正你注释里的lastrow代码，再用它构建动态数据源：

• 你之前写的Worksheets(WS)是错误的，因为WS已经是工作表对象，直接用WS.Cells(WS.Rows.Count, 1).End(xlUp).Row就能拿到A列最后一行有数据的行号。
• 用WS.Range("A2:A" & lastRow)生成从A2到最后一行的动态范围，再把这个范围转换成带工作表名的引用字符串，作为下拉列表的数据源。

另外可以优化重复代码，用循环批量处理E9-E11三个单元格，避免重复写三次删除和添加验证的逻辑：

修改后的完整代码：

'Select and update new AccessionGroups
Sub Update()
    Dim WS As Worksheet
    Dim WS2 As Worksheet
    Dim lastRow As Long
    Dim targetCell As Range
    Dim listSource As String
    
    '指定工作表对象
    Set WS = Sheet1
    Set WS2 = Sheet2
    
    '获取Sheet1中A列最后一行有数据的行号
    lastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row
    '构建动态下拉数据源的完整地址（带工作表名，避免跨表引用问题）
    listSource = "='" & WS.Name & "'!" & WS.Range("A2:A" & lastRow).Address
    
    '批量处理E9、E10、E11三个单元格
    For Each targetCell In WS2.Range("E9:E11")
        '先删除原有验证规则，避免冲突报错
        targetCell.Validation.Delete
        '添加新的下拉列表验证
        targetCell.Validation.Add xlValidateList, , , listSource
    Next targetCell
End Sub

2. Validation.Add xlValidateList后三个逗号的原因 #

Excel VBA中Validation.Add方法的完整语法是：

expression.Add(Type, AlertStyle, Operator, Formula1, Formula2)

各参数的作用：

• Type：必填项，指定验证类型，这里xlValidateList表示下拉列表验证。
• AlertStyle：可选项，验证失败时的提示样式（比如警告、停止），留空则用默认值。
• Operator：可选项，验证逻辑的运算符（比如大于、等于），下拉列表验证不需要这个参数，所以留空。
• Formula1：必填项（对下拉列表来说），指定下拉列表的数据源，就是你代码里后面跟着的地址字符串。
• Formula2：可选项，仅当Operator需要第二个对比值时用，下拉列表不需要，留空。

你代码里的三个逗号，是跳过了AlertStyle、Operator这两个可选参数，直接给第四个参数Formula1赋值，所以用逗号来占位。

内容的提问来源于stack exchange，提问作者mdatawolf