Improve speed to populate Listbox based on a Textbox entry

Hello experts,

Below piece of code works perfectly with the data in most of my sheets (thanks to this forum )

Currently I'm dealing with a sheet that has wayyyyy more rows to loop through and it takes quite a while to get the desired results in the listbox.

Is there a way to speed up this process? If so, what do I need to do?
(I have been reading articles about array's but I'm not familiar with that)

Here is the current code

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi Mama,

Its not overly clear how your originally populating your listbox data. Easiest way is if your data is stored in an actual worksheet then you can filter the range by your search team, populate an array and finally populate your listbox.

Below code assumes your data is on sheet(1) and your data is in column A:D

Please Login or Register  to view this content.

Try

Please Login or Register  to view this content.

Kind regards
Leo

Hi bradl822,

Thanks for your reply. You are right about me not being clear with regards to the data (I apologize)
All data is in a table on 1 sheet in the workbook. The total number of columns is 81 (a lot, i know)

The listbox gets populated with the first 9 columns of the table and upon entering a value into the textbox all 9 columns will be searched.

For filtering prior to the actual search of a value in the textbox I used to have optionbuttons on the form (somewhat like you are suggesting)
That did'nt work on a operational level as the user (helpdesk) mostly doesn't know if the data there looking for is an address, city, country, customernumber, equipmentnumber and so on.

They get partial information by phone and skipping the optionbuttons by just entering a value in the textbox made the form user-friendly and easier.

It really worked great with only about 1000 rows but due the huge increase of rows it has gotten slower. That's why I'm trying to speed things up a bit.

Anyway....thanks for your thoughts on my request.

Regards,
Mama

Hi LeoTaxi,

Thank you for your reply. I'm trying to understand what the code is doing by debugging (and trial and error)

I think if i could limit the array (arr) to the 9th column (so it would take into account A - I) it would enhance speed a lot as there are now 81 columns in the CurrentRegion.

Please Login or Register  to view this content.

Is that possible?

Regards,
Mama

Hi Mama,
For Only 9 Columns

Please Login or Register  to view this content.

Kind regards
Leo

@LeoTaxi and Bakerman2

Thank you, both solutions do speed up the search.

You make it look so simple!

Can you tell me how I can adjust my code so that upon selecting a row in the Listbox all (81) textboxes get populated?
This is the code I have, using the List/Listindex.
As a result of "limiting" the number of columns to search in the ListBox to 9, populating textboxes will stop after 9 with my code.

Please Login or Register  to view this content.

That's it! Of course.

All working smoothly and swift now!

Thank you so much for your help, time and patience!

Regards,
Mama