ComboBox Filter from Array based on Another ComboBox Value

[Iain A]

Hi,
Been searching for a way of Using a ComboBox that looks at a Worksheet Array of Countries (col A) / Cities (col B). If I select a Country the ComboBox should show all those Cities for that Country.
I have tried the following which works, but have 100s of Countries and multiple Cities for each Country

If cboCountry.Value = "AFGHANISTAN" Then Me.cboRegion.List = Sheet5.Range("B2:B6").Value

I have attached a stripped down file and would appreciate any assistance with this problem, apologies if this has already been covered, but cannot find anything in other threads.

 

[Chihiro]

Basic concept:
You should either filter original range, or iterate over it, checking for the condition and fill result array, and use that to populate cboRegion.

Sample code using array to iterate and check for condition.

Private Sub cboCountry_Change()
'Clear the region combobox
Me.cboRegion.Value = " "

Dim arr, resArr
Dim iCount As Long, i As Long, j As Long
With Sheet5
    arr = .Range("A2:B" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
    iCount = Application.CountIf(.Range("A:A"), Me.cboCountry.Value)
End With
ReDim resArr(1 To iCount, 1 To 1)
For i = 1 To UBound(arr)
    If arr(i, 1) = Me.cboCountry.Value Then
        j = j + 1
        resArr(j, 1) = arr(i, 2)
    End If
Next

Me.cboRegion.List = resArr

End Sub

Edit: Alternately you can use advanced filter copy on another sheet to extract matches and use that to fill combobox.

 

[Iain A]

Thanks Chihiro you are a star and this has saved me so much time.