?
Solved

Excel search very slow

Posted on 2011-10-19
8
Medium Priority
?
186 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:msb20
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 36995044
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
 

Author Comment

by:msb20
ID: 36995278
i didn't get you
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36995321
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:msb20
ID: 36995425
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
 
LVL 12

Accepted Solution

by:
kgerb earned 2000 total points
ID: 36995591
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
 

Author Comment

by:msb20
ID: 36996350
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36999518
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
 

Author Comment

by:msb20
ID: 37009412
thanks so much
may god bless you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question