Listbox conditional Adding Items

[sujittalukde]

I am trying to populate a list box from a range if a condition is matched.
The data arrangement is given under

product	salesman	units	criteria
fax	brown	1	y
phone	smith	10	y
fax	jones	20	y
fax	smith	30	n
p	jones	40	n
pc	smith	50	y
fax	brown	60	y
phone	davis	70	n
pc	jones	80	y

I’m using the following code

Private Sub CommandButton1_Click()
    Dim rng1 As Range
    Dim oCell As Range
    Dim sRng As Range
 
    Set rng1 = Sheets("sheet1").Range("D1:D10")
    With ListBox1
        .ColumnCount = 4
    End With
    For Each oCell In rng1
        If oCell.Value = "y" Then
            If sRng Is Nothing Then
                Set sRng = oCell.EntireRow
            Else
                Set sRng = Application.Union(sRng, oCell.EntireRow)
            End If
        End If
    Next oCell
    If Not sRng Is Nothing Then ListBox1.List = sRng.Value
 
 
End Sub

But this is only adding the items till a ‘n’ is found in the list.

I want the code should populate all the rows which satisfies ‘y’ in the column D

A workbook is attached.

I am using Excel 2003.

[mikerickson]

Rather than build a discontinuous range (with way more columns than a listbox)

For Each oCell In rng1 
    If oCell.Value = "y" Then 
        With oCell.EntireRow
            .AddItem .Range("A1").Value
            ListBox1.List(ListBox1.ListCount - 1, 1) = .Range("B1").Value
            ListBox1.List(ListBox1.ListCount - 1, 2) = .Range("C1").Value
            ListBox1.List(ListBox1.ListCount - 1, 3) = .Range("D1").Value
        End With
    End If 
Next oCell

The stopping at the first "n" is because the range you are building is discontinuous and only the first Area of that range is getting to the listbox.

[sujittalukde]

Thanks. its working.