Remove Listbox item if match not found in VBA

[rajesh2014]

Hello

I am writing vba code that will search all listbox items against a entire column in a sheet.
If listbox item not found in Excel sheet column, i want to delete the item from list. I tried few codes, its showing some error as "Could not get list property, Invalid property array index". Below is my code i am using currently.

Private Sub CommandButton1_Click()
Dim itemExistResults As Boolean
Dim myarray()
Dim intItem As Long

myarray = Application.Transpose(Sheet1.Range("a2:a1000"))

For intItem = 0 To ListBox1.ListCount - 1
    If IsInArray(ListBox1.List(intItem), myarray) Then
    Else
    ListBox1.RemoveItem intItem
    End If
Next

End Sub

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = UBound(Filter(arr, stringToBeFound)) > -1
End Function

I have attached my file for reference. In my file, if button clicked, it will open userform, there we can see list box which is loaded from sheet2. Once i clicked button it should remove all items which is not exist in Sheet1 A column.

Any assistance would be great.

 

[Hui]

Why not search and remove the item as the list box's list is being added

 

[rajesh2014]

Why not search and remove the item as the list box's list is being added

Thanks Hui for reply.

I made it working by changing loop

For intItem = ListBox1.ListCount  To 0  step - 1
   If IsInArray(ListBox1.List(intItem), myarray) Then
   Else
    ListBox1.RemoveItem intItem
   EndIf
Next