Removing Duplicates from ComboBox sourced from Table

I have a combobox which is populating value from a table column. The column contains multiple rows including rows with duplicate values. I want the combobox to contain only unique values.

The code I used to populate combobox:

Set wb = Workbooks.Open(Filename:=ThisWorkbook.Path & "Data.xlsx")
Set sh = Worksheets("Sheet1")
Set tbl = sh.ListObjects("Table1")
combobox.list = tbl.ListColumns(1).DataBodyRange.Value

I tried using RemoveDuplicates but always gives a Type mismatch or Object Required error.

CodePudding user response：

Please, try the next way:

Sub ComboUniqueVal()
 Dim wb As Workbook, sh As Worksheet, tbl As ListObject, arr, El, dict As Object
 Set wb = Workbooks.Open(fileName:=ThisWorkbook.path & "Data.xlsx")
 Set sh = wb.Worksheets("Sheet1")
 Set tbl = sh.ListObjects("Table1")
 Set dict = CreateObject("Scripting.Dictionary")
 arr = tbl.ListColumns(1).DataBodyRange.Value 'for faster iteration
 For Each El In arr
    dict(El) = 1
 Next
 ComboBox.list = dict.Keys
End Sub