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]

Actually combobox list is not showing all searched records only shows which have the search string in start but not showing the search string record which have it in middle or last part of name

Sorry, I don't understand your searching criteria, your explanation kind of confusing. So, please explain in more detail.
And one thing, do you need to search by 1 keyword only?

 

[shakaib26]

Sorry, I don't understand your searching criteria, your explanation kind of confusing. So, please explain in more detail.
And one thing, do you need to search by 1 keyword only?

Hi
As per my sample excel sheet which i shared on mega.nz
there are two names in the coulumn C
"Khawaja Muhammad Shakaib" and "Ajief Khawaja"
now i search the keyword "Khawaja"
then in combobox list results it shows only "Khawaja Muhammad Shakaib" but not shows the "Ajief Khawaja"
this is the problem. and now search is case sensitive like it search only "Khawaja" but not search is typed "khawaja" in small letters.
Thanks

 

[shakaib26]

Hi
Akuini
How are you?
Is it possible to search two or three words exits in difference places in a setense through combobox?

Example Setnese: The quick fox jumps over a lazy dog.
Search words: 1- dog 2- dox or 3- jumps