How to only show items that are bigger then 0 in a listbox

[Waimea]

I have a listbox that I populate with rowsource from a table called table1.

I am trying to only load values that are bigger then 0 and not negative in the listbox.

I am trying to select which column that is bigger then 0 and not negative.

Ex.

I want to load only values in the multiple column listbox for all values in column D that are greater then 0.

So if row5 in the rowsource is 0 then row 5 won't get loaded into the listbox etc. for all rows.

This is the code I have right now:

Private Sub CommandButton11_Click()

Dim lastrow As Integer
Dim X As Integer

Dim ws As Worksheet
Set ws = Sheets("Sheet1")

lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

For X = 2 To lastrow

     ' Column D
     If ws.Cells(X, 4) > 0 or ws.cells(X,4) <> "0" Then
        '''''''''''''''''''
        ''' Do something here to update the rowsource???
    End If
End Sub

How can I do this?

 

[Waimea]

My Aswer Is This,

your code did exactly what I asked for. I will try to be more specific in the future!

How would I add to this code?

Private Sub UserForm_Initialize()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, rr As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   rr = Application.CountIf(Sheets("sheet1").Range("D:D"), ">0")
   ReDim Nary(1 To rr, 1 To 14)
   rr = 0
   For r = 2 To UBound(Ary)
      If Ary(r, 4) > 0 Then
         rr = rr + 1
         For c = 1 To 14
            Nary(rr, c) = Ary(r, c)
         Next c
      End If
   Next r
   ReDim Preserve Nary(1 To rr, 1 To 14)
   Me.ListBox1.ColumnCount = 14
   Me.ListBox1.List = Nary
End Sub

I have tried changing the range in rr and the coulmn in ary(r,4) which works.

What if I wanted to check if Ary(r,4) and Ary(r,5) > 0?

 

[My Aswer Is This]

This code was supplied by Fluff. I believe it's best to wait till tomorrow and he will have another answer for you. If I start trying to help with a answer and others on the forum start providing answers to the same question I normally move on to help someone else. I'm sure he will be able to help you. But I will keep watching.

 

[Waimea]

What do I have to do to access Nary in another private sub or sub?

 

[Waimea]

I found that I can assign public variables in the top of a module!

I am still curious about how to check for multiple conditions with arrays!

 

[Fluff]

You can check for two conditions like

   For r = 2 To UBound(Ary)
      If Ary(r, 4) > 0 And Ary(r, 5) > 0 Then
         rr = rr + 1
         For c = 1 To 14
            Nary(rr, c) = Ary(r, c)
         Next c
      End If
   Next r

 

[Waimea]

Thank you for your reply Fluff!

I don't understand this line in your code:

rr = Application.CountIf(Sheets("sheet1").Range("D:D"), ">0")

Can I have multiple ranges?

 

[Fluff]

That's simply there to find the size of the new array, to prevent having empty rows in the listbox

 

[Waimea]

Ok, thank you for your reply Fluff!

 

[Fluff]

You're welcome