none
How to add 2 ranges' values to the same list RRS feed

  • Question

  • For example I have Range 1:

          A

    1   11

    2   12

    3   13 

    And Range 2:

          B

    1   14

    2   15

    I want to add these ranges to the same list, but when I try to use validation data list, I will get error messages about I need to use the same range to create the list.

    If there is a workaround for me to create a list with code?

    Thanks.

    Wednesday, December 27, 2017 5:51 AM

Answers

  • Hello SE13KJ1S,

    I would suggest you use VBA macro to combine value from different range into one array and then use the array to set validation in specific cell.

    Here is a simply code for setting validation in C1.

    Sub CombineRange()
    Dim xlSheet As Worksheet
    Set xlSheet = Worksheets("Sheet1")
    Dim xlRange1 As Range
    Dim xlRange2 As Range
    'get two range object
    Set xlRange1 = xlSheet.Range("A1:A3")
    Set xlRange2 = xlSheet.Range("B1:B2")
    
    Dim xlArray As Variant
    ReDim xlArray(1 To 1) As Variant
    'add value from range 1 to the array
    For i = 1 To xlRange1.Cells.Count
    xlArray(UBound(xlArray)) = xlRange1.Cells(i).Value
    ReDim Preserve xlArray(1 To UBound(xlArray) + 1) As Variant
    Next i
    'add value from range 2 to the array
    For i = 1 To xlRange2.Cells.Count
    xlArray(UBound(xlArray)) = xlRange2.Cells(i).Value
    ReDim Preserve xlArray(1 To UBound(xlArray) + 1) As Variant
    Next i
    'remove blank, last value from the array
    ReDim Preserve xlArray(1 To UBound(xlArray) - 1) As Variant
    'set the validation for C1
    With xlSheet.Range("C1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(xlArray, ",")
    End With
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SE13KJ1S Thursday, December 28, 2017 6:18 AM
    Wednesday, December 27, 2017 6:22 AM

All replies

  • Hello SE13KJ1S,

    I would suggest you use VBA macro to combine value from different range into one array and then use the array to set validation in specific cell.

    Here is a simply code for setting validation in C1.

    Sub CombineRange()
    Dim xlSheet As Worksheet
    Set xlSheet = Worksheets("Sheet1")
    Dim xlRange1 As Range
    Dim xlRange2 As Range
    'get two range object
    Set xlRange1 = xlSheet.Range("A1:A3")
    Set xlRange2 = xlSheet.Range("B1:B2")
    
    Dim xlArray As Variant
    ReDim xlArray(1 To 1) As Variant
    'add value from range 1 to the array
    For i = 1 To xlRange1.Cells.Count
    xlArray(UBound(xlArray)) = xlRange1.Cells(i).Value
    ReDim Preserve xlArray(1 To UBound(xlArray) + 1) As Variant
    Next i
    'add value from range 2 to the array
    For i = 1 To xlRange2.Cells.Count
    xlArray(UBound(xlArray)) = xlRange2.Cells(i).Value
    ReDim Preserve xlArray(1 To UBound(xlArray) + 1) As Variant
    Next i
    'remove blank, last value from the array
    ReDim Preserve xlArray(1 To UBound(xlArray) - 1) As Variant
    'set the validation for C1
    With xlSheet.Range("C1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(xlArray, ",")
    End With
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SE13KJ1S Thursday, December 28, 2017 6:18 AM
    Wednesday, December 27, 2017 6:22 AM
  • Thank you very much.
    Thursday, December 28, 2017 6:18 AM