Excel VBA loop slow

[shakaib26]

Hi everyone,

i have 80000 rows in "c"column with date in it. i am running below mentioned code to add the combobox list items on combobox change basis. When i run the macro it takes too long time on each change.

Can anybody suggest what to do to speed up the code.


Private Sub ComboBox2_Change()
Dim ws As Worksheet
Set ws = Worksheets("word by word")
Dim Lastrow, r, i As Long

For i = ComboBox2.ListCount - 1 To 0 Step -1
ComboBox2.RemoveItem i
Next i

Lastrow = ws.Range("c" & Rows.Count).End(xlUp).Row
For r = 3 To Lastrow
If InStr(ws.Cells(r, 3), ComboBox2.Text) > 0 Then
ComboBox2.AddItem (ws.Range("c" & r))

'code for showing list unique values only
Dim vArr()
ReDim vArr(1 To ComboBox2.ListCount)
vArr = ComboBox2.List
Dim e
With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In vArr
If Not .exists(e) Then .Add e, Nothing
Next
If .Count Then ComboBox2.List = (.keys)
End With

End If
If ComboBox2.Text = "" Then
ComboBox2.Clear
End If
Next r
End Sub

 

[Akuini]

Can you help more if i want to search the characters at start of word no need to search in mid or end of the word?

Can you give us some examples?

 

[shakaib26]

Can you give us some examples?

Hi
Thanks for your reply

Like there are 3 parts in a name
Name: Khawaja Muhammad Shakaib
I need it should show results only on starting characters like khaw, muh, shak ie.......
But not shows results of mid characters like waja, mmad, aib ie......
Hopefully you will have understand what i want.

 

[shakaib26]

Hi
Thanks for your reply

Like there are 3 parts in a name
Name: Khawaja Muhammad Shakaib
I need it should show results only on starting characters like khaw, muh, shak ie.......
But not shows results of mid characters like waja, mmad, aib ie......
Hopefully you will have understand what i want.

Like i start my search from waja, mmad, aib so, it should not show the results.

 

[Akuini]

Hi
Thanks for your reply

Like there are 3 parts in a name
Name: Khawaja Muhammad Shakaib
I need it should show results only on starting characters like khaw, muh, shak ie.......
But not shows results of mid characters like waja, mmad, aib ie......
Hopefully you will have understand what i want.

Do all names have 3 parts?

 

[shakaib26]

Do all names have 3 parts?

No this is only example. Name can be also in 1 word

 

[Akuini]

If you type "mu khaw" do you expect it to match "Khawaja Muhammad Shakaib"? so the order of the keywords is irrelevant?

 

[shakaib26]

If you type "mu khaw" do you expect it to match "Khawaja Muhammad Shakaib"? so the order of the keywords is irrelevant?

It should match only by typing "khaw"
If i search on muh ie.... Then it will also bring irrelevent words or names which last or mid characters are matching
Like there are two names "Khawaja Muhammad Shakaib" and other is "Muhammad Aslam"
I need names with khawaja if i search with keyword "muh" then it will bring khawaja but also bring "Muhammad Aslam" which i don't need and it will also increase the combobox list volume.

 

[Akuini]

So the keyword order matters, right?
Keyword "khaw mu" should match "Khawaja Muhammad Shakaib" but keyword "mu khaw" shouldn't.
Keyword "khaw" should match "Khawaja Muhammad Shakaib" but not "Shakaib Khawaja"
Try replacing "Sub get_filterX(ary)" with this:

Sub get_filterX(ary)
'search without keyword order, case insensitive
'Like there are 3 parts in a name
'Name: Khawaja Muhammad Shakaib
'I need it should show results only on starting characters like khaw, muh, shak ie.......
'But not shows results of mid characters like waja, mmad, aib ie......

Dim i As Long, x, z, q, sv
Dim v As String
Dim flag As Boolean
    
    d.RemoveAll
    z = Split(UCase(ComboBox1.Value), " ")

    For Each x In ary
        flag = True: v = UCase(x)
        sv = Split(v, " ")
            If InStr(1, v, z(0), vbBinaryCompare) = 1 Then
                For i = 1 To UBound(z)
                    If i > UBound(sv) Then
                        flag = False: Exit For
                    Else
                         If InStr(1, sv(i), z(i), vbBinaryCompare) <> 1 Then flag = False: Exit For
                    End If
                Next
            Else
                flag = False
            End If
            
        If flag = True Then d(x) = Empty
    Next

End Sub

 

[shakaib26]

T

So the keyword order matters, right?
Keyword "khaw mu" should match "Khawaja Muhammad Shakaib" but keyword "mu khaw" shouldn't.
Keyword "khaw" should match "Khawaja Muhammad Shakaib" but not "Shakaib Khawaja"
Try replacing "Sub get_filterX(ary)" with this:

Sub get_filterX(ary)
'search without keyword order, case insensitive
'Like there are 3 parts in a name
'Name: Khawaja Muhammad Shakaib
'I need it should show results only on starting characters like khaw, muh, shak ie.......
'But not shows results of mid characters like waja, mmad, aib ie......

Dim i As Long, x, z, q, sv
Dim v As String
Dim flag As Boolean
   
    d.RemoveAll
    z = Split(UCase(ComboBox1.Value), " ")

    For Each x In ary
        flag = True: v = UCase(x)
        sv = Split(v, " ")
            If InStr(1, v, z(0), vbBinaryCompare) = 1 Then
                For i = 1 To UBound(z)
                    If i > UBound(sv) Then
                        flag = False: Exit For
                    Else
                         If InStr(1, sv(i), z(i), vbBinaryCompare) <> 1 Then flag = False: Exit For
                    End If
                Next
            Else
                flag = False
            End If
           
        If flag = True Then d(x) = Empty
    Next

End Sub

Thanks

Now it's perfect as per my requirement. Great work.
But only one problem in combobox list its showing only name which is in the start but it is now showing which is in 2nd or 3rd part of name, but when i populate the list data to listbox there is shows all name even 1st, 2nd or 3rd.

 

[Akuini]

but when i populate the list data to listbox there is shows all name even 1st, 2nd or 3rd.

How do you populate the list data to listbox?