I have a sheet at work that is constantly adding rows to add more
items and their details to it.
Items are added and associated by a category in A4:A9999.

I want to have a combo box that will only show the rows for a given
category, then hide rows not equal to the value chosen.

So:
a) determine all distinct values in A4:A9999 and populate the combo
box with these values in real time if that won't be too hard on the
CPU, but somehow update it.
b) Then, when the user chooses a value from the combo box, all rows
are hidden except the A:A cells equal to the value and the A1:A3
(headers)

I am stuck on how to do a).

any ideas? thanks

Use advance Filters like below

Sub MakeComboBox()

With Sheets("Sheet1")
.ComboBox1.Clear

If .FilterMode Then
.ShowAllData
End If

.Columns("A").AdvancedFilter _
Action:=xlFilterInPlace, _
unique:=True

Set UniqueData = .Columns("A").SpecialCells(xlCellTypeVisible)
For Each FltData In UniqueData
If FltData.Value < "" Then
.ComboBox1.AddItem FltData.Value
End If
Next FltData

If .FilterMode Then
.ShowAllData
End If

End With
End Sub




"Finny" wrote:

I have a sheet at work that is constantly adding rows to add more
items and their details to it.
Items are added and associated by a category in A4:A9999.

I want to have a combo box that will only show the rows for a given
category, then hide rows not equal to the value chosen.

So:
a) determine all distinct values in A4:A9999 and populate the combo
box with these values in real time if that won't be too hard on the
CPU, but somehow update it.
b) Then, when the user chooses a value from the combo box, all rows
are hidden except the A:A cells equal to the value and the A1:A3
(headers)

I am stuck on how to do a).

any ideas? thanks