VBA设置Excel单元格验证时数据范围异常问题求助

问题原因 #

MsgBox显示的验证公式是=Verwijzingen!A1:A6，但实际生效的却是=Verwijzingen!A2:A7，核心原因是Excel数据验证的Formula1参数默认会将普通单元格引用解析为相对引用。你的目标单元格区域从B2开始，相对于B2的位置，A1属于「向上1行、向左1列」的相对位置，Excel会自动将这个相对引用应用到每个目标单元格，最终导致整个列表范围下移一行。

解决方案 #

方法1：使用绝对引用

修改Formula1的拼接逻辑，在引用的行号和列号前添加$符号，强制转为绝对引用，避免自动偏移：

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=" & ws1.Name & "!$A$1:$A$" & aantalrijen2

方法2：使用命名区域

先给验证列表范围定义一个工作簿级命名区域，再直接引用这个名称，这种方式更直观且不会出现偏移问题：

' 先定义命名区域
ThisWorkbook.Names.Add Name:="ValidationSource", RefersTo:=ws1.Range("A1:A" & aantalrijen2)

' 数据验证中引用命名区域
With .Range("B2:B" & aantalrijen).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=ValidationSource"
End With

额外优化建议 #

原代码中计算行数的方式ws1.Range("A1", ws1.Range("A1").End(xlDown)).Cells.Count存在风险：如果A1单元格为空，End(xlDown)会直接跳到工作表最后一行，导致行数计算错误。建议改用更稳定的写法：

aantalrijen2 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row

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