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