Populate Data Validation w/ corresponding values

[BrianExcel]

This is complicated (I think) so bare with me...

In Column C are part numbers
In column D is a supplier name

Column C's part numbers vary in qty. In other words, one vendor name may appear 25 times in column D because they have 25 part numbers available. But another vendor only appears 5 times because they only have 5 parts.

Right now, a combo box is populated with the vendor name. What I need, is when that vendor name is chosen (the combo box corresponds to the vendor names in Column D) I need ALL part numbers associated with that vendor to populate into a cell using data validation.

Right now I am using the following code, which hard codes specific words into the data validation, but I need to dynamically change the options based on the name I select in the drop down box above.

Does this make sense? Feel free to ask more clarifying questions if needed.

Public Sub DropDownCode()
Dim i       As Long, _
    LR      As Long, _
    Choices As String
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

LR = Range("D" & Rows.Count).End(xlUp).Row
    Choices = "Cookies, Apples, Choices"
For i = 12 To LR Step 4
    With Range("D" & i).Validation
        .Delete
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Choices
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With
End Sub

Thanks for any help in advance!

 

[Comfy]

You could filter Column D by the supplier chosen, Then copy the visible items to a hidden column and then use that column to apply the validation for the 2nd drop down.