apologise this is reply to another messsage.


R.VENKATARAMAN wrote in message
...
see help under
do while
do until
=============
Sam S via OfficeKB.com wrote in message
...

I have a sheet with product item numbers in column B and product names
in
column C and each of these products has a category name associated with
it
in
column A on the same row. There are several hundred products but only 20
different categories.

In a lisbox -
How do I display only one entry for each category (20) and if one of
these
catagories is selected in the list box then [only] the products (Item
Number
and Product name) corresponding to the that selected category are
displayed
in another listbox?

*********

The code below works but only fills listbox2 with 1 column of values -
how
do
I modify this code to fill listbox2 with 2 columns of data instead of 1
(would also like listbox2 data sorted)

Thank you,

Sam


Private Sub ListBox1_Change()
Dim a(), i As Long, r As Range
With Sheets("sheet1")
For Each r In .Range("a1", .Range("a65536").End(xlUp))
If r = Me.ListBox1.Value Then
ReDim Preserve a(i)
a(i) = r.Offset(, 1).Value: i = i + 1
End If
Next
End With
With Me.ListBox2
.Clear
.List() = a
End With
Erase a: i = 0
End Sub


Private Sub UserForm_Initialize()
Dim dic As Object, x, r As Range
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("sheet1")
For Each r In .Range("a1", .Range("a65536").End(xlUp))
If Not IsEmpty(r) And Not dic.exists(r.Value) Then
dic.Add r.Value, Nothing
End If
Next
End With
x = dic.keys: Set dic = Nothing
Me.ListBox1.List() = x
End Sub