Optimise vba .find search loop through range of cells

  • 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.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!