Excel VBA create name from two different ranges to be used in list (data validation)

Question

Is it possible to create one name from two different ranges to be used in list (data validation)?

I have two columns: A which has content from rows 1 to 3 and C which has content from rows 1 to 4.

I tried using ThisWorkbook.Names.Add Name:="TheList", RefersTo:=Range("A1:A3,C1:C4") but when inserting =TheList in the data validation field, it says "The list source must be a delimited list, or a reference to a single row or column".

Is there a way to combine the values from both columns?

Answer 1

Since Excel requires a contiguous range of values to be used as a data validation list, you really have two options.

1. Create a separate (possibly linked), contiguous column of values in an unused area of the workbook. Adding a defined name for the range is suggested, but not required.

For this method, I'd build up a list way out in the "Z" column (or possibly in another, hidden worksheet). Here's an example:

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. The second method is to not use another range at all, just create a delimited list. This makes the list "hard-coded", but since it's created with VBA that may not be a hindrance in your case.

The key in this one lies in defining the list to the .Validation.Add method without an equals sign. For example, the validation list would be Red,Orange,Yellow,Green,Blue,Indigo,Violet. No double quotes. No spaces. No equals. Here's the example:

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