Insert sheet data into userform combobox with no duplication - Excel Vba

Question

I am making a excel userform with two comboboxes. One combox retrieves data from sheet3 (Column A) named "CompaniesandSubsidiaries". Then on the basis of first combobox, second combobox retrieves sub-category data. But problem is column A has data repetition. I have attached an image.

sampleimage

What I am trying is:

Private Sub Combobox1_DropButtonClick()

    Dim rng As Range

    Dim ws As Worksheet

    Set ws = Worksheets("CompaniesandSubsidiaries")

    For Each rng In ws.Range("Companies")

        Me.ComboBox1.AddItem rng.Value

    Next rng

End Sub

Private Sub ComboBox2_DropButtonClick()

    Dim rng As Range

    Dim ws As Worksheet

    Set ws = Worksheets("CompaniesandSubsidiaries")

    For Each rng In ws.Range("Providers")

        Me.ComboBox2.AddItem rng.Value

    Next rng
End Sub

I made a formula for both "column A and B" named companies and providers respectively. But It repeats the values. That I don't want.

What I want is to get the data dynamically from sheet3 for first combobox as company names (With no duplication)and then next combobox will enable and it will show the provider list corresponding with the company name.

Please provide a simple and easily understandable code. Thanks

Please clarify your specific problem by showing what you have tried and where you are stuck or add additional details to highlight exactly what you need. See the How to Ask page to get better response. — suvartheec
Can you show what you want it to look like instead? I'm still not sure I understand what the desired result is. — Hambone
@Hambone there are two combobox in the userform. One combobox would show the companies name, in this case Companies names would be {company1, company2, company3, company4} and when a user select one company then second combobox would automatically retrieves data from second columns that would be ABC, BCD,.....JKL in case of company1. And in case company of company4, the second combobox would show only ABC as provider. Hope you will get my point. — SHEIKH

Cheries Mewengkang Cheries Mewengkang · Accepted Answer · 2017-11-19T19:47:49

You can remove the duplicate data before fill it on ComboBox Below the code to remove duplicate value :

ActiveSheet.Range("Companies").RemoveDuplicates Columns:=1, Header:=xlNo

You can also manually store the Companies data to an array variable, iterate to each item on that array to check if it's duplicate or not. Here a link video that demostrate how to evaluate each data on an array and make distinct/ unique value. https://www.facebook.com/excelfuneasy/videos/693150624214021/

Insert sheet data into userform combobox with no duplication - Excel Vba

1 Answers