VBA Filter Criteria Readout

0
votes

Hej,

I am trying to readout the filter criteria available of a specific column (in this case column 4) and then use the criteria from that column to populate a combobox in a userform with it. I have tried a bit with arrays, but can't get it to work. Anyone been ther before?

Dim Value As Variant
Dim filtarr As Variant
Dim f As Integer

Me.CB_City.Clear    'Clears the combobox
Call wsDef      'reformats values throughout
wksP.Range("A1").AutoFilter field:=5, Criteria1:=Me.CB_Country.Value    'Filters the table depending on the country selects
If Me.CB_Country.Value > 0 Then
    Me.CB_City.Enabled = True
End If

'/////Loads in Cities from criteria list/////
    With wksP.AutoFilter
        With .Filters(4)
            For Each Value In .Filters(4).Criteria1     'Iterate through each criteria value
                filtarr(UBound(filtarr)) = Mid(Value, 2, Len(Value))    'Save criteria value to array variable Arr
                ReDim Preserve filtarr(UBound(filtarr) + 1)     'Increase array size with 1
            Next    'Continue with next criteria value
            ReDim Preserve filtarr(UBound(filtarr) - 1)     'Decrease array size with 1
        End With
    End With
arrayscriteriaautofilter