Dynamic combobox based on row from different sheet

Question

I've got 2 sheets in my workbook in Excel 2010. The first got a row with names, listed like this: Jens A. Christian Peter Jens A. Anders Jens A. etc.

On the second sheet I want to make a combobox, which show all the names from the first sheet, but without duplicates. Is that possible to make in VBA code?

In addition I want to make the list dynamic, but I guess I've just have to call the function in:

Private Sub Workbook_Open()

To make that happen?

Thanx in advance

See if this link sets you on the right path? siddharthrout.wordpress.com/2011/07/29/… — Siddharth Rout

Marcin Janowski Marcin Janowski · Accepted Answer · 2012-05-15T19:49:40

Plese check the below macro. It operates on 2 columns but you can adjust it.

Option Explicit
Sub UniqueRecords()

Dim i As Long
Dim j As Long
Dim k As Long
Dim bDuplicate As Boolean

i = 1
Do Until Cells(i, 1).Value = ""

  'check if record exist
  j = 1
  Do Until Cells(j, 2).Value = ""
    bDuplicate = False
    If Cells(i, 1).Value = Cells(j, 2).Value Then
      bDuplicate = True
      Exit Do
    End If
  j = j + 1
  Loop

  'add record if no duplicate
  If bDuplicate = False Then
    For k = 1 To ActiveSheet.Rows.Count
      If Cells(k, 2).Value = "" Then
        Cells(k, 2).Value = Cells(i, 1).Value
        Exit For
      End If
    Next k
  End If

i = i + 1
Loop

End Sub

Dynamic combobox based on row from different sheet

1 Answers