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_comb...istbox_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