non-duplicate combo box

[glen.e.mettler]

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

 

[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