populate ListBox depending on values in two columns

[johnsonk]

Hi,
I am trying to populate a listbox in a userform that ignores the value 0 in two colums C & D so it only populates the listbox with the rows that have a value greater than 0 in columns C & D. The image is an example of the data so what I would need from that is to just show the two rows. Hope this makes sense.

Regards

Also posted here Populate listbox in userform from worksheet ignoring 0 from two columns - OzGrid Free Excel/VBA Help Forum

 

[My Aswer Is This]

In your example:
populates the listbox with the rows that have a value greater than 0 in columns C & D
I only see row 2 which would meet this test

And are you saying you are using a multicolumn Listbox?

 

[johnsonk]

Hi,

I see what you mean and the way I want it will not work. The image below is what I would need it to populate from the data, is there a way to do this by colour of cells may be ? or may be another way.

Regards

 

[My Aswer Is This]

Hi,

I see what you mean and the way I want it will not work. The image below is what I would need it to populate from the data, is there a way to do this by colour of cells may be ? or may be another way.

View attachment 24259

Regards

So in this example what values would be loaded into the listbox?
And I asked are you using a multicolumn listbox and you did not answer that question

 

[johnsonk]

Hi,

Both rows would be loaded into the listbox and all four columns if that is what you mean by multicolumn.

Regards

 

[My Aswer Is This]

Hi,

Both rows would be loaded into the listbox and all four columns if that is what you mean by multicolumn.

Regards

Most listboxes only show one piece of data per row.
A multicolumn listbox can have many pieces of data per row just like a worksheet has numerous columns of data.

Now why are you saying both rows would be loaded into listbox?
So all rows in your worksheet will be loaded into the listbox columns 1 to 4

What do you plan to do with this data after you get it into the userform listbox?

Working with multicolumn listboxes is a little complicated so do you know what to do after you get the data into the listbox?

 

[johnsonk]

In the example data below the only rows to be loaded into the listbox would be rows 2 & 5 columns 1 to 4 as they contain a value greater than 0 in either column C or D rows 3 & 4 would be ignored.

The listbox will just sit on a homepage userform showing what Core is still to come back in.

 

[NoSparks]

Assuming the picture with all columns having headers is correct,
try this

Private Sub UserForm_Initialize()
    Dim lr As Long, lc As Long
    Dim filtrng As Range
    
Application.ScreenUpdating = False

With Sheet1
    'remove any existing filters
    If .FilterMode Then .ShowAllData
    'determine last row
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    'determine last column
    lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
    'put formula in next column
    Range(.Cells(2, lc + 1), .Cells(lr, lc + 1)).FormulaR1C1 = "=RC[-2]+RC[-1]"
    'filter on new column
    Set filtrng = .Cells(1).CurrentRegion
    With filtrng
        .AutoFilter field:=lc + 1, Criteria1:="<>0"
    End With
End With
    
'add temp sheet to copy to
Sheets.Add
With ActiveSheet
    .Name = "Scratchpad"
    filtrng.Offset(1).Resize(lr - 1, lc).Copy
    .Paste
    'populate list box
    Me.ListBox1.List = Sheets("Scratchpad").Cells(1).CurrentRegion.Value
    'remove sheet
    Application.DisplayAlerts = False
    Sheets("Scratchpad").Delete
    Application.DisplayAlerts = True
End With

'restore sheet1 to original
With Sheets("Sheet1")
    'remove filter
    filtrng.AutoFilter
    'If .FilterMode Then .ShowAllData
    'clear the added column
    .Columns(lc + 1).ClearContents
End With
            
Application.ScreenUpdating = True

End Sub

sample file at Box.com

Hope that helps

 

[johnsonk]

NoSparks,
That works perfect thank you so much.

 

[NoSparks]

You're welcome, glad to have helped.

Are you satisfied with your Searchable ComboBox question?
You never responded to my questions on it.