Fill Userform Combobox with unique values using named range

[InDenial]

Hi all,

I am trying to do something and it is probably very simple. I have been looking online for help and found a lot of interesting information but I am somehow not able to put the pieces together.

What I am trying to do:

I have a column which contains information. There are duplicates in this column.

Switch1
Switch2
Switch3
Switch2
Switch2
Switch4
Switch1

Since this column is variable in size I created a Named Range doing:

With Worksheets("Interfaces")
.Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Name = "Switches"
End With

Referring to this Named Range in a Combobox on a Userform will put everything in that Combobox. The problem is that I am getting duplicate values. To get rid of the duplicate values I somewhere read I have two options:

[ol]
[li]I use an advanced filter.[/li]
[li]I go through all items in the list and put them in a variable and populate the combobox that way.[/li]
[/ol]

I am not going for option 1 since I do not want to clutter the excel sheets with data and second somehowe I just can;t get it to work. (Maybe wrong reasons but I am stuborn and want to use vba for this)

I managed to write a little function which takes the named range and can do something with it.

I call the function using:

With CBSwitch
    .ListFillRange = CreateUniqueList(Range("Switches"))
End With

The function I call looks like:

Function CreateUniqueList(Rng As Range) As Variant
     Dim Element As Variant
    
     For Each Element In Rng
         MsgBox Element
             
     Next Element
End Function

SO my questions are:

1. How do I create a new named range based on the new unique values?
2. WHat should I send back to the call?

I would rather have pointers or corrections than complete answers. Thanks in advance..


InDenial

 

[SkipVought]

Hi,

Unique values can be accomplished EASILY, by at least THREE methods. The first method, you have already discovered.

Method 2: MS Query - on a separate sheet, add a query table using Data > Import External Data > New database query... this is a ONE TIME exersize. Each time your source list changes, refresh the query and rename the list using your code.

Method 3: PivotTable - on a separate sheet, add a pivot table. this is a ONE TIME exersize. Each time your source list changes, refresh the pivot table and rename the list using your code.

I would never take the time to code a solution like you are suggesting. IMHO, its a waste of time when there are other satisfactory solutions.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CautionMP]

PostInDenial,

In [tt]CreateUniqueList[/tt] you can use a Collection to build a unique "list" of values, then the Collection can iterated and the values added the ComboBox using [tt]AddItem[/tt].

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)