Dim hshA As Object
Dim i As Long
Set hshA = CreateObject("Scripting.Dictionary")
With ThisWorkbook.Sheets("Ziele")
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
hshA(.Cells(i, 1).Text) = 0
Next
Me.Strecke1.List = hshA.keys
End With
Set hshA = Nothing
Dim hshA As Scripting.Dictionary
Set hshA = New Scripting.Dictionary
Dim coll As New Collection
Dim cellValue As Variant
Dim i As Long
With ThisWorkbook.Sheets("Ziele")
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
cellValue = .Cells(i, 1).Value
If Not IsError(cellValue) Then ' Check if cell value is not an error
On Error Resume Next ' Skip error if item already exists in collection
coll.Add cellValue, CStr(cellValue) ' Convert to string to avoid type mismatch
On Error GoTo 0 ' Re-enable error handling
End If
Next i
End With
' Transfer items from collection to an array
Dim items() As Variant
ReDim items(1 To coll.Count)
For i = 1 To coll.Count
items(i) = coll(i)
Next i
' Fill ComboBox with unique items
Me.Strecke1.List = items
Private Sub Strecke2_Change()
Dim oDic2 As Object
Dim i As Long
Set oDic2 = CreateObject("Scripting.Dictionary")
Me.Strecke3.Clear
Me.Strecke4.Clear
With ThisWorkbook.Sheets("Ziele")
For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
If .Cells(i, 2) = Me.Strecke2 Then
oDic2(.Cells(i, 3).Text) = 0
End If
Next
Me.Strecke3.List = oDic2.keys
End With
Set oDic2 = Nothing
End Sub
Dim Bereich$
With ThisWorkbook.Sheets("Ziele")
Bereich = .Range(.cells(1, 2), .Cells(.Rows.Count, 1).End(xlUp)).Address
Me.Strecke1.List = Evaluate("SORT(UNIQUE(" & Bereich & "))")
End With
Dim Bereich$
With ThisWorkbook.Sheets("Ziele")
Bereich = .Range(.cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Address
Me.Strecke1.List = Evaluate("SORT(UNIQUE(" & Bereich & "))")
End With