ListBox for Specific Condition

[IoanZ]

Hellow to everyone.

Please help me.

I have this Range("A2:D4") with fruits and quantity

To load this Range in a Listbox Userform i use this code

Private Sub UserForm_Initialize()
ListBox1.RowSource = Sheets("Sheet1").Range("A2:D4").Address
End Sub


I want to know which is the vba code to put in this ListBox only the products with the quantity greater than 0 ( qant > 0 ) ?

Thank's a lot.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row/Coll[/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Quantity[/TD]
[TD]unit[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]20[/TD]
[TD]kg[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Oranges[/TD]
[TD]0[/TD]
[TD]kg[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bananas[/TD]
[TD]25[/TD]
[TD]kg[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[CalcSux78]

You can use a loop to identify what values to add to the listbox.

Private Sub UserForm_Initialize()
Dim arr As Variant
Dim r&, c&, n&
arr = [A2:D4]
ListBox1.Clear  'remove items
For r = LBound(arr, 1) To UBound(arr, 1)
    If arr(r, 3) > 0 Then
        ListBox1.AddItem arr(r, 1) 'add 1st item of "row"
        For c = 2 To 4
            ListBox1.List(n, c - 1) = arr(r, c) 'add add'l "columns"
        Next c
        n = n + 1
    End If
Next r
End Sub

For more info: https://excelmacromastery.com/vba-user-forms-2/#The_ListBox

 

[IoanZ]

Thank's a lot

 

[Fluff]

@IoanZ
For future reference
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[IoanZ]

Hello I did not know that I will read the rules with great care Thank you for your answer