Combo Box Duplicates

[cmonthetic]

Hi,

I am creating a form in Excel to enable easier searching for users of the spreadsheet.

I plan to have 3 combo boxes on the form.

The first combo box will be populated direct from the data in the main spreadsheet, with the other 2 combo boxes populated dependant on the data filtered from the selection made in Combo box 1.

The spreadsheet contains nearly 20000 rows and there is a lot of duplicated data within the column I would like to use for the first combo box.

Is it possible to only display unique values in the combo box from the selected range? and any ideas on how I would populate combo box 2 from the user selection made in combo box 1.

Any help would be greatly appreciated.

TIA

 

[SkipVought]

Hi,

take a look at Data > Filter > Advanced Filter -

Copy to another location
NO Criteria
Unique Values


Skip,

_[red][/red]

 

[bdmangum]

TIA,

It sounds to me like you are basically creating your own filter. You could insert an autofilter (data>filter>autofilter) to do the same thing as you are coding.

Anyways, here is one way you could populate your first combobox. There are others ways to accomplish this, but this way works. It's set up to only add items not already in the combobox. Keep in mind that I designed this code for a combobox/sheet which would only hold at a max 100 items. If your sheet has 20,000 items this code could be slow.

Private Sub UserForm_Initialize()

Dim Count As Long

    Count = 2

'Adds items to combobox, while preventing duplicate items
While Sheets(1).Cells(Count, 1).Value <> ""
    If ComboBox1.ListCount - 1 >= 0 Then
        For i = ComboBox1.ListCount - 1 To 0 Step -1
            If Sheets(1).Cells(Count, 1).Value <> ComboBox1.List(i) And i = 0 Then
                Me.ComboBox1.AddItem Sheets(1).Cells(Count, 1).Value
            ElseIf Sheets(1).Cells(Count, 1).Value = ComboBox1.List(i) Then
                Exit For
            End If
        Next i
    Else
        Me.ComboBox1.AddItem Sheets(1).Cells(Count, 1).Value
    End If
    Count = Count + 1
Wend

End Sub

You could populate the second combobox by a similar method. First you would need to filter the data based upon the user's combobox one selection (the advanced or autofilter would work, or you could write a code to do it). Then you would add the items the same way.

Hope this helps. Not sure I understand exactly what you are asking. Seems like adding an AutoFilter would solve your problem without having to create a form.

BD