ComboBox Duplicate Removal?

[BrianExcel]

I have a combo box on a userform populating via a named range, but I have a lot of N/As in the list and I'd like to remove all the duplicates.

Any suggestions how?

 

[sous2817]

Rather than using a named range, could you build an array looping through the named range and only add the valid entries. You'd then use the array to populate the combobox rather than the named range directly.

 

[BrianExcel]

Totally willing to do that, but could you advise on how? For example, if the sheet name is "Misc" and I need the range to go from cell A1 to the end of the data set?

 

[sous2817]

This seemed to work from me. Just change the "Name_Of_Range" to suit your situation.

Private Sub UserForm_Initialize()
Dim str(), foo
Dim k As Long

str = Application.Transpose(Range("Name_Of_Range"))
k = 0
For Each foo In str

    If foo <> "N/A" Then
        Me.ComboBox1.AddItem foo
    End If
Next foo

End Sub

 

[BrianExcel]

This is one of many ranges I am using. How do I designate this as a column so I can do the same code to set the other ranges in the same way? Many of them currently have duplicates and if this works I'd love to remove them...

 

[BrianExcel]

Also, the combo box is populated with a different range depending on a radio button being checked first.

In other words, the range can be populated in three different ways: using the indexed # (named range), using an internal company code (second named range) or using the vendor item description (third named range).

The internal description has too many duplicates, but the other two do not. I need to remove the duplicates from the internal range description only....

 

[sous2817]

Here's how I'd populate a combobox based on an option button selection:

Sub pop_Combobox()
Dim str(), foo
Dim k As Long

k = 0

With UserForm1

    Select Case True
        Case .OptionButton1
            str = Application.Transpose(Range("Name_Of_Range_Associated_with_Option1"))
        Case .OptionButton2
            str = Application.Transpose(Range("Name_Of_Range_Associated_with_Option2"))
        Case .OptionButton3
            str = Application.Transpose(Range("Name_Of_Range_Associated_with_Option3"))
    End Select

    For Each foo In str
        If foo <> "N/A" Then
            .ComboBox1.AddItem foo
        End If
    Next foo

End With

End Sub

Before you were talking about "N/A" values, now you mention duplicates. Are your N/A values duplicates, or do we need to loop through the array and pull out only uniques?

 

[BrianExcel]

Sorry for the delay, the N/As are duplicates.

Basically we could either. The range is from A1:A180. About 30 of the cells contain actual values, the rest are N/A.

I basically need to pull only the values, not the N/A.

 

[BrianExcel]

Also, can you interpret what the second line of your code does? I have never seen a dimmed value like that...

I am referring to the line:

Dim str(), foo

Thanks!!

 

[sous2817]

Then what I posted above should work. The option button that is selected dictates which named range gets used. From there, the range is loaded in as an array, and only non-"N/A" values are put in to the combo box.