Excel VBA 从要在列表中使用的两个不同范围创建名称（数据验证）

由于 Excel 需要将一系列连续的值用作数据验证列表，因此您确实有两种选择。

1. 在工作簿的未使用区域创建一个单独（可能链接）的连续值列。建议为范围添加定义的名称，但不是必需的。

对于这种方法，我会在“Z”列（或可能在另一个隐藏的工作表中）建立一个列表。下面是一个例子：

Option Explicit

Sub MakeValidationRange()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim dataRange As Range
    Set dataRange = ws.Range("A1:A3,C1:C3")

    Dim valRange As Range
    Set valRange = ws.Range("Z1")

    Dim entry As Variant
    For Each entry In dataRange
        valRange.Formula = "=" & entry.Address
        Set valRange = valRange.Offset(1, 0)
    Next entry
    Set valRange = ws.Range("Z1").CurrentRegion

    '--- alternate method using a named range
    ThisWorkbook.Names.Add Name:="TheList", RefersTo:=valRange

    Dim dropDownCell As Range
    Set dropDownCell = ws.Range("B1")
    dropDownCell.Validation.Delete
    dropDownCell.Validation.Add Type:=xlValidateList, _
                                AlertStyle:=xlValidAlertStop, _
                                Formula1:="=TheList"

    '--- alternate method without creating a named range
    Set dropDownCell = ws.Range("B2")
    dropDownCell.Validation.Delete
    dropDownCell.Validation.Add Type:=xlValidateList, _
                                AlertStyle:=xlValidAlertStop, _
                                Formula1:="=(" & valRange.Address & ")"

End Sub

2. 第二种方法是根本不使用其他范围，只需创建一个分隔列表。这使列表“硬编码”，但由于它是使用 VBA 创建的，因此在您的情况下可能不会成为障碍。

这其中的关键在于将列表定义为不带等号的.Validation.Add方法。例如，验证列表将为。没有双引号。空间不足。没有等号。这是示例：Red,Orange,Yellow,Green,Blue,Indigo,Violet

Option Explicit

Sub MakeValidationList()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    Dim dataRange As Range
    Set dataRange = ws.Range("A1:A3,C1:C3")

    Dim dataList As String
    Dim entry As Variant
    For Each entry In dataRange
        dataList = dataList & entry.Value & ","
    Next entry
    '--- remove the last trailing comma
    dataList = Left$(dataList, Len(dataList) - 1)

    Dim dropDownCell As Range
    Set dropDownCell = ws.Range("B3")
    dropDownCell.Validation.Delete
    dropDownCell.Validation.Add Type:=xlValidateList, _
                                AlertStyle:=xlValidAlertStop, _
                                Formula1:=dataList
End Sub