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

 

[shakaib26]

How do you populate the list data to listbox?

Private Sub ComboBox1_Change()
Dim ws As Worksheet
Set ws = Worksheets("word by word")
Dim serial As Long
With ListBox1
.Clear
.Font.Name = "al qalam quran majeed web"
.Font.Size = 14
.ColumnCount = 6
.ColumnWidths = "220,220,220,220,80,20"
.List = ws.Range("A2", "F2").Value
.AddItem
.List(0, 0) = "Roman Urdu Word"
.List(0, 1) = "English Translation Word"
.List(0, 2) = "Urdu Translation Word"
.List(0, 3) = "Quran Arabic Word"
.List(0, 4) = "Serial No."
.List(0, 5) = " "
.RemoveItem (1)

Dim r, LastRow As Long
LastRow = ws.Range("c" & Rows.Count).End(xlUp).Row
For r = 3 To LastRow
If InStr(ws.Cells(r, 3), ComboBox1.Text) > 0 Then
serial = serial + 1
.AddItem ws.Cells(r, "f").Value
.List(.ListCount - 1, 1) = ws.Cells(r, "e").Value
.List(.ListCount - 1, 2) = ws.Cells(r, "d").Value
.List(.ListCount - 1, 3) = ws.Cells(r, "b").Value
.List(.ListCount - 1, 4) = serial
.List(.ListCount - 1, 5) = " "
End If
Next r
End With
End Sub

 

[Akuini]

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

 

[shakaib26]

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

My data is in Arabic and Urdu language format, so i make on sample file in english data. Please see given link below.

32.5 KB file on MEGA

mega.nz

 

[Akuini]

There are 2 combobox, what are they for?

 

[shakaib26]

There are 2 combobox, what are they for?

combobox1 is getting text from combobox2 and populating the listbox.
Although there is no need of combobox1 and listbox can be populated directly from combobox2 but i used 2 combobox

 

[shakaib26]

combobox1 is getting text from combobox2 and populating the listbox.
Although there is no need of combobox1 and listbox can be populated directly from combobox2 but i used 2 combobox

if any body don't want to type then he can directly select from combobox1 list.

 

[Akuini]

I don't quite understand what you're trying to do.
1. What do you want to show in the listbox?
2. It would help if you can use an example and describe step by step what you're trying to do.

 

[shakaib26]

I don't quite understand what you're trying to do.
1. What do you want to show in the listbox?
2. It would help if you can use an example and describe step by step what you're trying to d

I have given you link of sample file. There you can understand just type "Khawaja" there and see how many results are showing in combobox list. And now search is case sensitive in this code.

 

[Akuini]

T

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.

Sorry, actually you should start a new thread on how to populate a listbox from a combobox, because it's a different question from the original.

 

[shakaib26]

Sorry, actually you should start a new thread on how to populate a listbox from a combobox, because it's a different question from the original.

Hi
Butt problem is not linked to listbox. 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
Even i do't populate the listbox and remove it then problem exist there.
Thanks