Combine multiple sources into one ComboBox list and show only uniques-CodePudding

I'm trying to combine two different lists into one ComboBox.list, but some of the values are identical and I don't want them to show up twice.

Unfortunately I'm not too experienced with VBA and the error messages are very generic, so I'm not sure why my attempts aren't working.

I found a code online that works well for combining the two, but when I add in the unique function it makes no difference. It combines the lists just fine, but it seems to ignore the unique function and so has a lot of duplicates.

This is the code that I've been using:

Sub Combo()

Set rng1 = sheet14.Range("Range_1")
     Set rng2 = sheet14.Range("Range_2")
    
     For Each cl In rng1
         If arStr = "" Then
             arStr = cl.Value
         Else
             arStr = arStr & "," & cl.Value
         End If
     Next cl
     
     For Each cl In rng2
         If arStr = "" Then
             arStr = cl.Value
         Else
             arStr = arStr & "," & cl.Value
         End If
     Next cl
  
    sheet13.SupplierCmb.List = WorksheetFunction.unique(Split(arStr, ","))

 End Sub

I've tested that the unique function works by using it on just one of the ranges and it works fine.

CodePudding user response：

I don't know the exact specifications for the Unique-function but could confirm that there is an issue with one-dimensional arrays.

I would suggest to use a dictionary to get the list - this will work also on older versions of Excel (but not on a Mac). You can simplify your code by creating a union of the ranges so that you have only one loop.

Dim dict As New Dictionary

Dim rng As Range, cell As Range
Set rng = Union(sheet14.Range("Range_1"), sheet14.Range("Range_2"))

For Each cell In rng
    dict(cell.Value) = ""
Next
sheet13.SupplierCmb.List = dict.Keys

Note that this is using early binding, so you need to set a reference to the Scripting runtime. If you don't want to do it, define the dictionary like this:

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

CodePudding user response：

You'd normally use a dictionary (as per the other answer) but since you called the worksheetfunction UNIQUE(), my guess is you may be able to use VSTACK() and TOCOL() too (latter in case of 2d-arrays) too. Though not available as a method in VBA, you can still evaluate the formula:

Sub Test()

Sheet13.SupplierCmb.List = Sheet14.Application.Evaluate("=UNIQUE(VSTACK(TOCOL(range_1, 3), TOCOL(range_2, 3)))")

End Sub

TOCOL() - There to 'flatten' each of the two range object in case they are multi-dimensional. The 2nd parameter '3' would ensure to leave out errors or empty cells;
VSTACK() - Vertically stack both flattened arrays;
UNIQUE() - Pass only unique items as a 1d-array back as a list.