How do I group data in an Excel Combo Box

[dogman_2000@hotmail.com]

I am using Excel 2002 and have several combo boxes on a worksheet.
Using 'ListFillRange' I have populated the contents of the Combo with
data from another worksheet.
The problem I have is that the 2 columns of data I am using have many
duplicates and I only want my users to see unique values.

Is there any way I can do this? I can't delete the duplicates because
there are other columns in my range with unique values.

Many thanks in advance.

[Gareth]

You could use ControlFormat.List and pass it a filtered array rather
than fill range.

Something along these lines:

'--------In a normal code module-------------
Public Const myListRange As String = "A1:A100"

Sub UpdateMyListBox()

Worksheets(1).Shapes(1).ControlFormat.List = _
fcnGetUniqueItems(Range(myListRange))

End Sub

Function fcnGetUniqueItems(rng As Range) As Variant

Dim c As Range
Dim UniqueItems() As String
Dim i As Integer

'place the first cell in your range in the array
ReDim UniqueItems(0)
UniqueItems(0) = rng.Cells(1)
For Each c In rng
For i = LBound(UniqueItems) To UBound(UniqueItems)
If c.Value = UniqueItems(i) And Not c.Value = "" Then Exit For
Next i
If i > UBound(UniqueItems) Then
ReDim Preserve UniqueItems(i)
UniqueItems(i) = c.Value
End If
Next c

fcnGetUniqueItems = UniqueItems
'You could also sort this array if you preferred

End Function
'-------------------------------------------

Note that this isn't dynamic. So if someone adds something new to the
range, the listbox isn't updated. Therefore, if this a problem you
should place the following code in the code module of the sheet where
your list is to trap any changes and update the listbox accordingly.

'------in sheet module where the source range is------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range(myListRange)) _
Is NothingThen UpdateMyListBox

End Sub
'------------------------


HTH,
Gareth


dogman_2000@hotmail.com wrote:
> I am using Excel 2002 and have several combo boxes on a worksheet.
> Using 'ListFillRange' I have populated the contents of the Combo with
> data from another worksheet.
> The problem I have is that the 2 columns of data I am using have many
> duplicates and I only want my users to see unique values.
>
> Is there any way I can do this? I can't delete the duplicates because
> there are other columns in my range with unique values.
>
> Many thanks in advance.
>