I need to take a column with a lot of redundant information and just take one of them

Question

I own a combo-box where I need him to go coluna "A" and take only one information of each (because it has redundancy of it), an example would be departamente in the case there is redundancy and not use a primary key

The code that feeds the combo-box:

Public Function carregaCombobox()
    cmbDepartment.Clear
    Worksheets("Full Control").range("A3").Select
    Dim itemLista As String
    Do While ActiveCell.Value <> ""
        If verificaItem(ActiveCell.Value) = False Then
            itemLista = ActiveCell.Value
            If cmbDepartment.ListCount >= 2 Then
                 cmbDepartment.AddItem itemLista
            Else
                cmbDepartment.AddItem itemLista
            End If

        End If
         ActiveCell.Offset(1, 0).Select
    Loop


End Function

But when I run it returns an error: '381': Could not get the List Property. Invalid Property array index.

He owns a função to check whether or not the information it takes from the cell selected by Do While which is this:

Public Function verificaItem(itemPlanilha As String) As Boolean
    Dim i As Long
    Dim itemLista As String
    verificaItem = False
    If cmbDepartment.ListCount > 0 Then
        For i = 0 To cmbDepartment.ListCount
            itemLista = cmbDepartment.List(i, 0)
            If itemLista = itemPlanilha Then
                verificaItem = True
                Exit Function
            End If
        Next i
    End If  
End Function

Exemplifying:

I have this information in a column

  A
Parede
Parede
Teto
Teto
Parede
Chão

And I just want the information without repeating it in a combo-box appearing then like this

Parede
Teto
Chão

Answer 1

If the Combobox is in a form, the code must be entered in the Subroutine Private Sub UserForm_Initialize() to load the Combobox data when initializing the form.

Code

Dim x As Long, ncell as Long
Dim Rng As Range, c As Range
Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("nome_planilha")

With ws

    ncell = .Cells(.Rows.Count, "A").End(xlUp).Row
    Me.ComboBox1.Clear

    For y = 1 To ncell
        Set c = .Cells(y, 1)
        Set Rng = .Range(.Cells(2, 1), .Cells(y, 1))
        x = Application.WorksheetFunction.CountIf(Rng, c)
        If x = 1 Then Me.ComboBox1.AddItem c
    Next y
End With

Variables

This code loops and checks the amount of values in which the current cell string appears, with the function CountIf. Add to Combobox only the first time the string appears in the column.

• ws: is the variable with the spreadsheet in which the data is
• ncell: is the last row filled in in column "A"
• c: is the current cell in which the loop is performed
• x: is the amount of elements of that string present in the range
• Rng: is the interval, which is incremented at each loop

Note: This code makes a loop in each line because it is easier. If the amount of data is too large, you need to fill an array with the column data and get the unique data in the vector, a function example that can be used:

Public Function unicoArr(ByRef Arr As Variant, x As Long) As Object
    Set unicoArr = New Collection
    Dim a
    Dim i As Long

    On Error Resume Next
    For i = LBound(Arr) To UBound(Arr)
        a = Arr(i)(x)
        unicoArr.Add a, a
    Next

End Function