• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Text box search filter

Juzar22

Member
Dear All

I have an excel file where i have a text box with VBA code to filter the search.
This excel has 4 column B.C.D.E.F

I have 2 Concerns

first the text box searches in column B only, i want to change code to search in complete sheet
for example if i search S 400 which is in column E then search is not working.

Second thing after first concern is solved , if the column D is filtered with 'SALES' then the text box filter ignores/Clears the column D filter
But i want search result after the filter in column D . For example if i filter column D 'SALES' then the search within this filter is not working.

Thank You
 

Attachments

  • Filter in cell.xlsm
    38.1 KB · Views: 9
What a mess this was to solve!
Concern 1
I've altered the code to search columns B, E & F only, but this can be altered by changing the fomula in cell J4:
=ISNUMBER(SEARCH($D$3,TEXTJOIN("|",TRUE,$B6,$E6:$F6)))
The part in red is what you alter if, for example you want to include Department (column D). If you include column C then you'll get unexpected results if you look only for digits because it treats the dates as decimal numbers (as if the date cells were formatted as 'General').
It uses advanced filter.

Concern 2
This was a bit more difficult, because, as you've found, fitering one way removes the original filtering, so what I've done is add a column to the table 'states' (which, by the way, I've enlarged to include all columns) headed 'x' in column G which I've then hidden.
The idea is that when you click your clear button, all filters are cleared and the table's dropdowns are enabled. This is where first you optionally filter your table using those drop downs in any combination you like. Once that's done you move to the textbox to start typing for specific text amongst mutliple columns, but the first thing it does is to record in column G with an 'x' those visible rows from your current filter, then it applies the advanced filter which not only filters for your sought text but also only shows rows with an x in column G.
So you have to do things in order!
 

Attachments

  • Chandoo43218Filter in cell.xlsm
    43.2 KB · Views: 7
I discover that the file you attached was created in Excel 2010 which I don't think has the TEXTJOIN function so in the attached is a version which doesn't need it:
=ISNUMBER(SEARCH($D$3,$B6 & "|" & $E6 & "|" & $F6))
I've included information for variations of this formula to cater for the columns you want included in the search in the light blue shaded area of the attached. The data in the light blue area are not needed for the smooth operation of the filters, so can be deleted.
 

Attachments

  • Chandoo43218Filter in cell v2.xlsm
    43.9 KB · Views: 5
Last edited:
I discover that the file you attached was created in Excel 2010 which I don't think has the TEXTJOIN function so in the attached is a version which doesn't need it:
=ISNUMBER(SEARCH($D$3,$B6 & "|" & $E6 & "|" & $F6))
I've included information for variations of this formula to cater for the columns you want included in the search in the light blue shaded area of the attached. The data in the light blue area are not needed for the smooth operation of the filters, so can be deleted.

@p45cal
Thank you for your thoughtfulness and appreciate your time on my concerns , this file works perfectly, Must say awesome work !
 
@p45cal
Only one issue i encountered the filter drop down button gets invisible after search, so what is hapeening i have to clear the comlete filter where i loose the filtered column.
can filter drop down be visible always so that without clear i can do filter on top of another filtered column without clearing complete filter.. Thanks
 

Attachments

  • Chandoo43218Filter in cell v2.xlsm
    41.3 KB · Views: 2
Test the attached.

Previously I shared with little data as a sample but now when i compiled the file ( see the attachment) which has total 7000 and above rows and now search is very slow and screen freezes for few seconds , for example if you search word ''Released'' then you will see the search is very slow and screen freezes,

can you please help on this , rest all is ok and works perfectly, Thanks
 

Attachments

  • 7000 rows.xlsm
    435.6 KB · Views: 9
Hi !​
Excel is slow - as it's very not a database software at least 50 times faster than Excel - and searching in all columns is slower​
than logically obviously just searching in the appropriate column only !​
As your data are within an Excel table, why don't you operate directly its inner columns filters ? (at child level)
According to your last attachment it needs 0.1 second to filter for 'S400', the same for 'Released', without any code neither any textbox !​
Another way without any textbox is to use an advanced filter, whatever for a column or all columns, try first manually to check its speed​
and when it works manually - easy as it's an Excel Basics - you can even use it by code …​
The more use of Excel as it was designed for, the faster !​
 
@Marc L is right.
If you revert to the advanced filter version I suggested in msg#2 it will be quick, but has its drawbacks.
The attached however is the result of some inelegant tweaks to the code I suggested in msg#6. It speeds up the process more than 40 times.
The code that was slowing things up was:
Code:
Set RngToHide = Union(RngToHide, databody.Cells(rw, 1))
The new code does far fewer Union operations.
 

Attachments

  • Chandoo43218Filter in cell v4.xlsm
    376 KB · Views: 12
@Marc L is right.
If you revert to the advanced filter version I suggested in msg#2 it will be quick, but has its drawbacks.
The attached however is the result of some inelegant tweaks to the code I suggested in msg#6. It speeds up the process more than 40 times.
The code that was slowing things up was:
Code:
Set RngToHide = Union(RngToHide, databody.Cells(rw, 1))
The new code does far fewer Union operations.

Thanks for helping me out, It works faster now.
 
Back
Top