populate combo box with conditions

[av8tordude]

This code populate the combo box with my list in column A. In column F has a status that is either "open" or "Closed" I will like to list only the names that are open. can someone offer assistance in revising my code. Thank you



Dim Rng As Range, LRow As Long


LRow = Range("A" & Rows.Count).End(xlUp).Row


cbName.Clear
For Each Rng In Range("A7:A" & LRow)
If Rng.Value <> "" Then cbName.AddItem Rng.Value
Next

[snb]

Avoid .additem to populate a combobox/listbox.
Use .List instead. See https://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html


Sub M_snb()
sn = Application.Transpose(Columns(1).SpecialCells(2))
sp = Columns(6).SpecialCells(2)

For j = 1 To UBound(sn)
If j < 7 Or sp(j, 1) = "closed" Then sn(j) = "|"
Next

cb.name.list = Filter(sn, "|", 0)
End Sub

[paulked]

Or



Dim LRow As Long
cbName.Clear
For LRow = 7 To Range("A" & Rows.Count).End(xlUp).Row
If Cells(LRow, 1) <> "" And Cells(LRow, 6) = "open" Then cbName.AddItem Cells(LRow, 1)
Next


If you want to ignore the case of 'open', eg 'OPEN' or 'Open' then put



Option Compare Text

at the top of the module

@snb I had't read the text with your post until after writing mine. Very interesting.

[paulked]

snb, is this an acceptable way?



Private Sub UserForm_Activate()
Dim LRow As Long, str As String
cbName.Clear
For LRow = 7 To Range("A" & Rows.Count).End(xlUp).Row
If Cells(LRow, 1) <> "" And Cells(LRow, 6) = "open" Then str = str & Cells(LRow, 1) & ","
Next
cbName.List = Split(str, ",")
End Sub




Obviously there will be a blank row at the end, but that's easily removed if it's a problem.

[snb]

Yes, but.....
- Using arrays prevents interaction with the workbook and is therefore far faster than checking cell by cell like you do.
- cbname.clear is redundant when you use the .List property.
- and Range("A" & Rows.Count).End(xlUp).Row has to be calculated too many times
- the list will contain an empty value at the end
So:

Private Sub UserForm_Activate()
sn=cells(1).currentregion.resize(,6)

For j = 7 To ubound(sn)
if sn(j,1)<>"" and sn(j,6) = "open" Then c00 = c00 & chr(0) & sn(j,1)
Next

cbName.List = Split(mid(c00,2), chr(0))
End Sub

[paulked]

Understood.

Thanks for the lesson :thumb