How to populate a combobox only with related values

[Pookiemeister]

I'm running a test on a userform I made up. I usually split up the main userform that would be used into sections and create another userform to test code because it easier to debug, in my opinion. Once I know the code works without any errors (preferably), I'll add it to the main userform that would be used.
So now I have a userform that contains one combobox(cmbPrdCde), a Submit button and a textbox(TxtBxBulkNum). In this example test, the values that will be used are located in Range("K3:O12"). Range("K1:O1") contain the title of each column I'll list them below for clarification.
Range("K1") = Product Code
Range("L1") = Product Name
Range("M1") = Report Description
Range("N1") = Packaging Size
Range("O1") = Bulk Number

In this test example, I have ten products listed. I only want to populate the combobox with the products that contain the same Bulk Number listed. I included part of the code that I've been using to populate the combobox with ALL the products. If possible I would like to modify the current code.

The code in between the commented "*" is code that I added and the below the bottom commented asterisks is the previous code. I need to somehow connect the two so they work together.

If Me.TxtBxBulkNum.Value <> "" Then
'*******************************************************************************************************************
        Dim foundBulk As Range
        Dim str3 As String
        str3 = Me.TxtBxBulkNum.Value
        Worksheets("Product_Info").Activate
        Set foundBulk = Worksheets("Product_Info").Range("K3", Range("O" & Rows.Count).End(xlUp)).Find(str3)
        
'*******************************************************************************************************************
        With ThisWorkbook.Worksheets("Product_Info")
            ary = .Range("K3", .Range("K" & Rows.Count).End(xlUp).Offset(, 1))
            ReDim nary(1 To UBound(ary))
                For r = 1 To UBound(ary)
                    nary(r) = ary(r, 1) & " (" & ary(r, 2) & ")"
                    Debug.Print nary(r)
                Next r
            Me.cmbPrdCde.List = nary
        End With
    Else
        With ThisWorkbook.Worksheets("Product_Info")
            ary = .Range("K3", .Range("K" & Rows.Count).End(xlUp).Offset(, 1))
            ReDim nary(1 To UBound(ary))
                For r = 1 To UBound(ary)
                    nary(r) = ary(r, 1) & " (" & ary(r, 2) & ")"
                    Debug.Print nary(r)
                Next r
            Me.cmbPrdCde.List = nary
        End With
    End If

I hope my explanation was detailed enough and thank you.

 

[Fluff]

How about

        With ThisWorkbook.Worksheets("Product_Info")
            ary = .Range("K3:O"& .Range("K" & Rows.Count).End(xlUp).Row)
            ReDim nary(1 To UBound(ary))
                For r = 1 To UBound(ary)
                    if ary(r,5)=str3 then nary(r) = ary(r, 1) & " (" & ary(r, 2) & ")"
                    Debug.Print nary(r)
                Next r
            Me.cmbPrdCde.List = nary
        End With

 

[Pookiemeister]

Thank you Fluff that worked with unexpected results. The unexpected results is that there are gaps(spaces) in between products inside the combobox. So how can I remove the gaps. Thank You

 

[Fluff]

That's a very good point. Declare i as Long & use

        With ThisWorkbook.Worksheets("Product_Info")
            Ary = .Range("K3:O" & .Range("K" & Rows.Count).End(xlUp).Row)
            ReDim Nary(1 To UBound(Ary))
                For r = 1 To UBound(Ary)
                    If Ary(r, 5) = str3 Then
                        i = i + 1
                        Nary(i) = Ary(r, 1) & " (" & Ary(r, 2) & ")"
                        Debug.Print Nary(i)
                     End If
                Next r
            Me.cmbPrdCde.List = Nary
        End With

 

[Pookiemeister]

I saw that you responded but I haven't tried your code yet. One thing that I forgot to ask and I'm not sure if the above code already does this so my apologies if it does. That being said, I would need the code to remove ALL spacing inside the combobox (Before listed items, in between listed items, and after listed items) Is this even possible? Thank you again

 

[Fluff]

If you mean blank rows in the combo, then yes it should (depending on your data). It will only be populated with the values from K/L if col O equals str3.

 

[Pookiemeister]

I just tried your code and it erased the spacing before and in between the items but added the blank spacing at the end of the items listed in the combobox. Thank you for all your help.

 

[Fluff]

If you don't want the blanks at the end, just add this line just before populating the combo

ReDim Preserve Nary(1 To i)

 

[Pookiemeister]

Thank you Fluff. Your the best! Quick question, even though I pretty have an idea what it does but what does adding the word "Preserve" do to the Nary array?

 

[Fluff]

It keeps the values that are already in the array whilst resizing it. Without that you would effectively clear the array.