Excel search very slow

Good evening everybody
i have an excel database contains around 1425 rows
when i'm searching using textbox it takes more time to filter the data
is there a way to speed up a technique of search?

any help I would appreciated.

 Book1.xlsm
msb20Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kgerbChief EngineerCommented:
I would probably try automating an autofilter.  Something like the code below.

I would also suggest making it a little less dynamic.  Running the code on every key stroke seems a little excessive.  Maybe have a filter button that executes the code or trap the enter key so the user can just hit enter after he types the desired filter.  Just some ideas.

Kyle
Sub ApplyFilter()
Dim Crit As String
Crit = "=*split*"
Columns("A:B").AutoFilter
Range("$A$1:$B$1425").AutoFilter Field:=2, Criteria1:=Crit, Operator:=xlAnd
End Sub

Sub ClearFilter()
Range("$A$1:$B$1425").AutoFilter Field:=2
End Sub

Open in new window

0
msb20Author Commented:
i didn't get you
0
kgerbChief EngineerCommented:
Which part did you not get?  Do you understand how to use the code I provided?

In my procedure the string variable Crit is the filter criteria.  You would need to read the data from your text box and use it as the filter criteria (the Criteria1 parameter on line 6).  Did you try runnign the code to see what it does?

Please be more specific on what you need help with and I'll do my best to explain it.

Kyle
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

msb20Author Commented:
sorry i tried alot but got nothing
so if you can edit the uploaded file i provided  i'll be more appreciated
thx alot
0
kgerbChief EngineerCommented:
Take a look at sheet2 of the attached file.  Type some text into the Criteria cell (F1) and hit enter.  The list automatically filters the values for whatever contains that text.  Look at the code and see if you understand what I did.  Capturing a worksheet change event is a good way of trapping an {enter} without key trapping.

Kyle
Q-27405130-RevA.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
msb20Author Commented:
it works fine thanx alot

btw i'm using software programmed by vb using ms access database it is very fast in searching using the same technique mentioned in the example file
what is the different between that soft and my excel db?

I  appreciate your help
0
kgerbChief EngineerCommented:
You're welcome.

Without seeing exactly how your access database is set up I can say for sure but I have a feeling the difference is looping through arrays or recordsets vs ranges in Excel.  Looping through arrays is very fast.  You can loop through millions of records in a second.  Looping through a excel range on the other hand is incredibly slow.  I'm not exactly sure why but it is.  If you were to change your code so that instead of looping through the range and hiding rows you added all your data to an array, looped through the array, deleted the appropriate entries based on the filter, and then dumped your array back onto the sheet it would be much much faster.  The fewer times you can read/write values from the worksheet the better off you'll be (generally).

Kyle
0
msb20Author Commented:
thanks so much
may god bless you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.