1

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.

crazyboy24
  • 45
  • 6

1 Answers1

1

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
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • how would i save the table in a variable, as I want to use it for Vlookup function when Combobox value is changed? – crazyboy24 Oct 05 '21 at 10:10
  • @crazyboy24 The table is in a variable... In order to use it in other Subs/Functions, you should declare it on top of a standard module (in the declarations area): `Public tbl As ListObject`. And delete the declaration from this `Sub`. After setting it in the above Sub, you can use it wherever you need. – FaneDuru Oct 05 '21 at 10:15
  • `Private Sub comboboaxbox_Change() Me.textbox.value = Application.WorksheetFunction.VLookup(Me.combobox.value, tbl, 3, False) End Sub` I am using this to retrieve the value of column 3 of the same above table. I have declared tbl as Public. I am getting "1004. Unable to get the Vlookup property of the WorkSheet Function class" error. @FaneDuru – crazyboy24 Oct 05 '21 at 10:31
  • @crazyboy24 You cannot use it as you try. You need there a range, not a table object. Try `tbl.DataBodyRange`. But this is a different question and our rule here states to post another question, supposing that the above suggestion will not work as you need. It is also a matter of the same format... – FaneDuru Oct 05 '21 at 10:55