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

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