Delete Duplicate Data for a combo box

[Mileshigh]

Ok so i have a list of supplier in column A that start in A2 and go down as new rows are always added. What i cant seem to work is i want to select all entrys from A2 down and paste them is lets say sheet2 and then remove duplicate names. Once only one of each company name is left i want to name that range: "comp" and use it for a combo box list.

I understand how to populated the combo list and how to copy the data to sheet2 just not delete the duplicate names leaving only one of each name...and then get the range named.

Like I said new company names are added so i need this to dynamic...i got the code working to remove the prior named ranges already workin

thanks

 

[craig.penny]

Try this

Sub Unique()
Dim s1 As Worksheet: Set s1 = ActiveWorkbook.Sheets("Sheet1") 'source sheet
Dim s2 As Worksheet: Set s2 = ActiveWorkbook.Sheets("Sheet2") 'target sheet
Dim Col1 As Long: Col1 = 1 'source sheet
Dim Col2 As Long: Col2 = 1 'target sheet
Dim FR1 As Long: FR1 = 2 'source sheet
Dim FR2 As Long: FR2 = 1 'target sheet
Dim LR As Long: s1.Select: LR = s1.Cells(s1.Rows.Count, Col1).End(xlUp).Row 'source sheet
Dim i As Long
Dim Collect As New Collection
Dim RngName As String: RngName = "comp"
On Error Resume Next
For i = FR1 To LR
      Collect.Add s1.Cells(i, Col1), CStr(s1.Cells(i, Col1))
Next i
On Error GoTo 0
For i = 1 To Collect.Count
      s2.Cells(FR2 + i - 1, Col2) = Collect(i)
Next i
s2.Range(s2.Cells(FR2, Col2), s2.Cells(FR2 + Collect.Count - 1, Col2)).Name = RngName
End Sub