non-duplicate combo box

[[email protected]]

I have about 1000 rows of data.
There are anywhere from 3 to 5 categories among the data (it changes
weekly)
I want to set up a combo box that displays only the appropriate
categories based on the available data that the user can select to
apply a filter.

How can I do that?

Glen

[HS Hartkamp]

With a pivot-table ?

Bas Hartkamp


<[email protected]> schreef in bericht
news:[email protected]...
>I have about 1000 rows of data.
> There are anywhere from 3 to 5 categories among the data (it changes
> weekly)
> I want to set up a combo box that displays only the appropriate
> categories based on the available data that the user can select to
> apply a filter.
>
> How can I do that?
>
> Glen
>

[Harald Staff]

Hi Glen

There are several ways. This one is fun because it's so awfully brutal:

Sub FillCbo()
Dim C As Collection
Dim L As Long
Set C = New Collection
On Error Resume Next
For L = 1 To 2000
If Sheets(1).Cells(L, 1).Value <> "" Then _
C.Add Sheets(1).Cells(L, 1).Value, _
Sheets(1).Cells(L, 1).Value
Next
Sheets(1).ComboBox1.Clear
For L = 1 To C.Count
Sheets(1).ComboBox1.AddItem C(L)
Next
End Sub

HTH. Best wishes Harald


<[email protected]> skrev i melding
news:[email protected]...
> I have about 1000 rows of data.
> There are anywhere from 3 to 5 categories among the data (it changes
> weekly)
> I want to set up a combo box that displays only the appropriate
> categories based on the available data that the user can select to
> apply a filter.
>
> How can I do that?
>
> Glen
>