I am searching from a userform, the user types in a textbox and as they type the search updates with each keystroke by updating a list of matches in a listbox (when you click on the listbox you open the related .pdf).
On a sheet with 200 rows and 12 columns of data (around 2400 cells) this works seamlessly, I am now searching on a sheet with 2000 rows and 3 columns and it is very slow (20 to 30 seconds) and causing screen lockups on the first keystroke but tends to be faster in subsequent strokes (I guess due to number of matches returned). I have seen reference to "binary search" and bubblesort as being optimal for this purpose but I cannot get my head around them yet.
If someone could point me in the right direction with respect to whether my expectations are too high (for each keystroke to update seamlessly) or whether there is a way to optimise the code and point me in the right direction that would be much appreciated.
Sub formfindmeVGWD() ' called from VGWDSS.spectrasch_change Dim findthis, lastcolumn, LastRow, rw, firstaddress, srchme Dim Asheet As String Dim FoundInRowNumb As Integer Dim fnd As Boolean Application.ScreenUpdating = False Asheet = VGWDSS.SPECTRATYPE.Value 'identifies the sheet, I have tried to use the .codename here but cannot then get it to reference correctly later. LastRow = Sheets(Asheet).Range("$A$1").End(xlDown).Row 'identifies last row in selected sheet (assumes all in A are filled sequentially) lastcolumn = Sheets(Asheet).Range("$A$1").End(xlToRight).Column 'identifies last column in selected sheet (assumes all in row 1 are filled sequentially) lastcell = Cells(LastRow, lastcolumn).Address 'identifies last cell from above information srchrnge = "$A$2:" & lastcell 'sets search range from A2 to last identified cell, avoiding header row findthis = VGWDSS.SPECTRASCH.Text ' this where you type the search query VGWDSS.VGWDRESULT.Clear ' clears the result list in preparation for filling, causes problems if this is removed. fnd = False With Sheets(Asheet).Range(srchrnge).Cells 'loops through the cells in the identified range using the .find function, it updates for each keypress. First keypress is taking a long ' time - upto 25 seconds and often locks up the screen. Set rw = .Find(What:=findthis, After:=Sheets(Asheet).Range("$A$2"), LookIn:=xlValues, lookat:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) If Not rw Is Nothing Then i = 0 firstaddress = rw.Address Do fnd = True If FoundInRowNumb <> rw.Row Then With VGWDSS.VGWDRESULT .ColumnCount = 3 .ColumnWidths = "60;60;120" .AddItem .List(i, 0) = Sheets(Asheet).Range("$A$1").Offset(rw.Row - 1, 0) .List(i, 1) = Sheets(Asheet).Range("$A$1").Offset(rw.Row - 1, 2) .List(i, 2) = Sheets(Asheet).Range("$A$1").Offset(rw.Row - 1, 1) i = i + 1 End With FoundInRowNumb = rw.Row End If Set rw = .FindNext(rw) Loop While Not rw Is Nothing And rw.Address <> firstaddress End If If fnd = False Then End If End With Application.ScreenUpdating = True End Sub