1

I'm currently trying to create a filter/sorting UI. I'm unfortunately quite limited when it comes to preventing duplicates before filling the Listbox. Hence the question is if there is a way to remove them afterward.

I'll include my attempt at fixing my issue.

Kind regards and thanks for the help.

With Sheets("Tabelle1")
lngZeileMax = .Cells(.Rows.Count, 11).End(xlUp).Row
Set rngBereich = .Range(.Cells(2, 11), .Cells(lngZeileMax, 11))
End With
    
Me.Caption = "Anlagekategorie"

With Me.ListBox1
.List = rngBereich.Value
.MultiSelect = fmMultiSelectMulti
End With

**Dim k As Long
Dim j As Long
With ListBox1
    For k = 0 To .ListCount - 1
    For j = .ListCount - 1 To (k + 1) Step -1
        If .List(k) = .List(j) Then .RemoveItem
        End If
    Next j
    Next l
End With**

End Sub

Danny
  • 11
  • 1
  • 1
    Load the range in a dictionary and [remove the duplicates](https://stackoverflow.com/questions/11870095/remove-duplicates-from-array-using-vbas) from it before inserting it in the listbox – cybernetic.nomad Dec 16 '21 at 15:32
  • I actually [answered](https://stackoverflow.com/a/70311282/14608750) a very similar question recently. My function `DistinctOf` takes a ParamArray, but you could change it to accept a 2D array. Then you would just alter your line `.List = DistinctOf(rngBereich.Value)` – Toddleson Dec 16 '21 at 15:43
  • I just tested it and you can change the declaration line to `DistinctOf(Strings As Variant)` and then you can do `.List = DistinctOf(rngBereich.Value)` and it works. Correctly loads in all the unique values into the listbox – Toddleson Dec 16 '21 at 16:11

0 Answers0