Pick List with Criteria - Collection - Fill Form Picklist

[mmetzinger]

I am looking for a way to fill a collection with values only if the value in the next column is a 1. I currently have this code which works great but I don't know how to give it criteria like a "where" statement that checks the next columns value.

    Dim AllCells As Range
    
'   The items are in A1:A20
    Set AllCells = Range("A1:A20")
    'Only input values if the value in column B is 0

Any help?

 

[mmetzinger]

Here is my full code for review

Sub populatelist()

    Dim AllCells As Range
    Dim Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item
    
'   The items are in A1:A20
    Set AllCells = Range("A1:A20")
    'Only if the value in column B is 0
    

    On Error Resume Next
    For Each Cell In AllCells
        NoDupes.Add Cell.Value, CStr(Cell.Value)
    Next Cell

    On Error GoTo 0

'   Sort the collection (optional)
    For i = 1 To NoDupes.count - 1
        For j = i + 1 To NoDupes.count
            If NoDupes(i) > NoDupes(j) Then
                Swap1 = NoDupes(i)
                Swap2 = NoDupes(j)
                NoDupes.Add Swap1, before:=j
                NoDupes.Add Swap2, before:=i
                NoDupes.Remove i + 1
                NoDupes.Remove j + 1
            End If
        Next j
    Next i
    
'   Add the sorted, non-duplicated items to a ListBox
    For Each Item In NoDupes
        UserForm1.ComboBox1.AddItem Item
    Next Item

'   Show the UserForm
    UserForm1.Show
    
End Sub