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?

 

[My Aswer Is This]

Do not use RowSource

Try this:

Private Sub CommandButton2_Click()
'Modified  2/27/2019  8:42:25 AM  EST
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
ListBox1.Clear
    For X = 2 To lastrow
        ' Column D
     If ws.Cells(X, 4) > 0 Or ws.Cells(X, 4) <> "0" Then
        ListBox1.AddItem Cells(X, 4).Value
    End If
Next
End Sub

 

[Waimea]

Hello My Aswer Is this,

thank you for your reply. Your code works great but I have a multi column listbox that I set using the rowsource?

I have 14 colums and I read that I can set it with

ListBox1.ColumnCount = 14

.

I am moving the goal post I guess but what if I want to add all 14 columns but not where column D is 0 or negative?

Perhaps I should try to create a new worksheet, delete the rows with 0 or negative and then add items to the listbox?

 

[Waimea]

I found this code,

Private Sub UserForm_Initialize()
    Dim Sh As Worksheet
    Set Sh = Worksheets("Sheet1")
    Dim Rng As Range


    With Sh
        Set Rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    


    Me.ListBox1.ColumnCount = 14
    
    Dim myArray As Variant
    myArray = Rng.Resize(, Me.ListBox1.ColumnCount).Value
    
    Me.ListBox1.List = myArray
End Sub

This works but I am not sure where to add the loop to check for 0 or "0"?

 

[My Aswer Is This]

I just showed you how do single column Listbox.

Your original post did not specify how many columns you wanted in your list box or what columns would be used to load the listBox

I have no way of helping without more details.

You cannot use RowSource but then exclude certain values in the range as far as I know.




Using a 14 column listbox and loading all the values greater then zero and then using those values in the listbox can require a lot more code.

 

[Waimea]

You are correct and I apologize for not being more specific.

From this post https://www.mrexcel.com/forum/excel...-columns-listbox-post1454491.html#post1454491 I have the following code:

Private Sub UserForm_Initialize()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim r As Integer
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    r = 0
    For Each Cell In Rng
        With Cell
            ListBox1.AddItem .Value
            ListBox1.List(r, 1) = .Offset(0, 1).Value
            ListBox1.List(r, 2) = .Offset(0, 2).Value
            ListBox1.List(r, 3) = .Offset(0, 3).Value
            ListBox1.List(r, 4) = .Offset(0, 4).Value
            ListBox1.List(r, 5) = .Offset(0, 5).Value
            ListBox1.List(r, 6) = .Offset(0, 6).Value
            ListBox1.List(r, 7) = .Offset(0, 7).Value
            ListBox1.List(r, 8) = .Offset(0, 8).Value
            ListBox1.List(r, 9) = .Offset(0, 9).Value
            'ListBox1.List(r, 10) = .Offset(0, 10).Value
            'ListBox1.List(r, 11) = .Offset(0, 11).Value
            'ListBox1.List(r, 12) = .Offset(0, 12).Value
            'ListBox1.List(r, 13) = .Offset(0, 13).Value
            'ListBox1.List(r, 14) = .Offset(0, 14).Value
          End With
        r = r + 1
    Next Cell
End Sub

This code however gives me an 438 error that I don't understand?

 

[Fluff]

How about

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

 

[Waimea]

Hi Fluff,

flawless!

It does exactly what I want!

Thank you!


One addition,

If I wanted to search the new rowsource / "Nary" with a textbox, how would I proceed?

 

[Fluff]

You're welcome & thanks for the feedback.

As for your addition as that's a completely different question, you need to start a new thread.
Thanks

 

[My Aswer Is This]

See I knew I could get you a answer.

Hi Fluff,

flawless!

It does exactly what I want!

Thank you!


One addition,

If I wanted to search the new rowsource / "Nary" with a textbox, how would I proceed?