Combo Box - Display Unique Items in List

I have a small fixed size table (10 rows x 9 columns) with company name, department name, company number, etc. entered on each row. These companies are all involved on a specific project and usually change for each project. A particular company can be listed in the table multiple times each having a different department name. I have several combo boxes that I would like to display a unique list of these company names listed in the table. Does anyone know how this could be done?
Thanks,
Gary S

Gary

Have a look at the file I attached to this post

http://excelforum.com/showthread.php?t=611074

I pretty much does the same sort of thing - create a unique list of names for a dropdown. It does go further and makes things dependent for subsequent comboboxes, but it should get you started.


rylo

Originally Posted by Leith Ross

Hello Gary,

Here is another method. This macro loads a Drop Down by name with only the unique values from the range specified. Place this code in a Standard VBA Module.

'Written: August 18, 2007
'Author: Leith Ross
'Summary: Loads the named Forms Drop Down control with only unique values
'         from the specified range.


Sub LoadUniquesInDropDown(Rng As Range, Drop_Down As Excel.DropDown)

  Dim Cell As Range
  Dim Data()
  Dim N As Long
  Dim SortRmg As Range
  Dim Wks As Worksheet
  
    Set Wks = Rng.Parent
    
      Rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
      
      Set SortRng = Rng.SpecialCells(xlCellTypeVisible)
      Wks.ShowAllData
      
      For Each Cell In SortRng
        ReDim Preserve Data(N)
        Data(N) = Cell
        N = N + 1
      Next Cell
      
      With Drop_Down
        .RemoveAllItems
        .List = Data()
      End With
      
End Sub

Macro Example - This could be attached to a button

Sub LoadAllDropDowns()

 ' This loads uniques in range A1:A10 of the active sheet into
 ' the drop down named "Drop Down 1" on "Sheet1"
    LoadUniquesInDropDown Range("A1:A10"), WorkSheets("Sheet1").DropDowns("Drop Down 1")

 ' Repeat the above method for each drop down you want to load
 ' Note: Drop Downs must be prefixed with the Worksheet they
 '        belong to. A Range without a prefix is assumed to be 
 '        on the Active Sheet.
  
End Sub

Sincerely,
Leith Ross

Thanks for your response. Appreciate the help.
Gary S

Originally Posted by rylo

Gary

Have a look at the file I attached to this post

http://excelforum.com/showthread.php?t=611074

I pretty much does the same sort of thing - create a unique list of names for a dropdown. It does go further and makes things dependent for subsequent comboboxes, but it should get you started.


rylo

Thanks for your response. This is helpful.
Gary S