VBA Advanced Filter - Filter any Excel Database 2013
HTML-код
- Опубликовано: 22 июл 2024
- www.onlinepclearning.com
VBA Advanced Filter - Advanced Filter any Excel Database with multiple criteria. In this short project we will be demonstrating how you can filter any flat file database with the advanced filter.
Particularly we will be looking at doing this with multiple flexible criteria.
To automate this process we will be recording the process and developing simple VBA code that we can assign to a button on our worksheet.
This video not just provides example of an advanced filter, but also other concepts such as macros, assigning buttons to macros to filter and clear data, and functions such as Offset. Very informative.
Trevor, My good man, I'm 79 years old and don't like to code when I can pick and choose. Therefore, I really like your presentation as it is going to save me some wear and tear on my cogs. Keep up the good work. I'm getting myself MSO 2021 on DVD for Christmas. I'm a retired coder of many languages, but I'm amazed as what you can do with Excel and Open Office.
I remember watching this video a long time ago and lost it, and after some time wanted to get back to it and could not find it. Fortunately today it happened and it saved the day. Thank you for making this video and sharing the knowledge.
Dear Mr. Trevor , You are one of the greatest professionals in this world
Really too appreciated from your tutorials
Hi Trevor, This session was extremely useful. I was trying more complicated way of coding with combo box, drop down and was not so successful. Thanks a lot for keeping it simple yet so useful.
This saved my VBA project from being a complete FAILURE! THANK YOU SOOOO MUCH!
This video helped me out so much! This is the key for making sharp and efficient dashboards. Thanks a lot!
Dear Trevor - this tutorial was amazing, thank you!
THANK YOU!!!!! After literally WEEKS of researching, online training and assistance from the experts at Mr Excel, this tutorial has FINALLY resolved my issue of creating a filter for my data. This has made it so easy to follow along, and really easy to understand. You are amazing, and have saved me SO MUCH TIME!!!!!
Thanks.
Trev
Ive been trying to do this for days. Thanks a lot for this tutorial.
All i can say is, Thank you very so much, it helped me a lot!
Same
Hi Trevor, Happy New Year!
A question, Does the source data need to be ordered by a certain criteria?
Thanks trev you've been an angel in my thesisstory ! thanks a lot ! cheerz belgium
Thank you for this video kind sir!!! Your teaching level is fantastic!!! God Bless You!
Thank you very much. Well done. It goes to the point with very clear comments and examples
Thanks Trevor, your method is a lot easier than using VB, Thanks again
Great one .. really helped me in my projects :)
Thanks Trevor
Hi Trevor! Great tutorial!
Is this also applicable to all text/numbers data? (eg. individual's contact details)
Since my data does not contain dates, what formula should be used in the criteria range?
Thanks for the help!
I have to thank you from the bottom of my heart for this tutorial! I have searched high and low to look for what i needed and this perfectly explains precisely what I needed! If I could shake your hand in person I would!!!!! THANK YOU! :)
You are a wonderful teacher. Thank you so much!
Just amazing, I love the trick u used to enable advanced filter to copy the data to another worksheet!
Hi Giang,
Thanks for the feedback. Appreciated...
Trev
Excellent Tutorial.. This is very useful....Much appreciated..Thanks Trevor..
Hi Hari,
Glad you liked it.
Trev
Excellent tutorial Trevor. Thank u! I did everything in the video for my data but mine stopped working after i filtered it a couple of times. What can I do?
You're a Legend!
Trevor, Do you have plans for any other Advanced Filter Videos?
I've found this video to be very helpful. Thanks for your expertise.
Hi! Thank you for sharing the video. Do you think it will work if I only want a subset of fields?
This is fantastic!!. I mean all your videos about excel. Thank you its a great help.. God Bless.. Keep it UP^ Sir.
Hi Trevor, Super helpful tutorial! I do have one question though. I am using this to filter a larger data set to display data only related to a specific rep (Sales Rep Name is my List and Criteria). I noticed, that if that field is left blank, and we click the Filter Data button, it pulls in everything. I want to prevent my users from doing that. Is there anyway to restrict this so that there must be something added to the criteria field, or a way to restrict it from pulling the entire data set?
Hi, thanks for your tutorial.
If i want exact match not only similar what should i do?
Hi Trevor, Thank you so much for the video.
Is it possible that out of entire Raw data we can filter only required column.
For example, Out of all Raw data I need Data only for Received data, Company, Amount and Type by applying AND filter for Type. I mean data for only four column by applying filter on anyone of them.
Thats just awesome, thank you very much for the wonderful information.
Thank you so much. Can you tell me how I can do this with one criteria in multiple sheets?
Thanks Trevor,
It's realy worth to watch your tutorials.
Great one.......
Expecting lot more from you...... :) :) :)
pavan kumar
Thanks
Much appreciated.
Trev
Brilliant tutorial, very handy for work :)
العفو منك لا اجيد الانجليزية بشقيها كتابتاً أو نطقاً ولكن استمتعت من طريقة شرحك على الفيديو وإظهار ما تقوم به من خلال تقريب الشاشة , لذا وجب عليا شكرك لنشر العلم وايصالة لمن لا يفهم الانجليزية بشكل بيسط
مع خالص تحياتي
من اليمن
Hi trevor, I am working on a similar project with advanced data validation. I have 4 filter boxes and 1st one is a dynamic name range, 2nd one is dependent on the first via offset(cascade, match...) function. Third is a dynamic & 4th is also a dynamic. When i recorded advanced data val for these 4 just as you explained in the video, only the 1st 2 filters work! The 3rd & 4th do not apply/execute at all.. any idea whats going on?
Very Helpful! Thanks a lot Trevor!
Thank you for this video. It saves me a lot of time. I really appreciate your hard work. Take Love
Very well explained... helped tremendously... Thnx for sharing
Hi Mr Trevor thank you for the excellent tutorial. However, I have been experiencing an issue with the FilterMe Macro, where after copying it into the interface, several rows of data goes missing. Hence there are missing data when I run the FilterMe Macro. Hope you can help me on this. Thanks!
Trevor, great video. Thanks.
Wow! This is one I was looking for
awesome tutorial.. but i need your help.. my "interface" sheet need number of filtered data (B9 and down below). and below the filtered data i need a row for the place and date and name. thank u
Been looking for this for a long time as this will help me a lot. Where I am now stuck is after filtering, I want to record another macro to copy the text that’s been filtered and paste onto another sheet. When I run the macro, it only copies and paste the amount of rows I selected when I recorded the macro. Any tips would be appreciated. That is for all your videos.
You are awesome man ! :)
Hi Trevor,
I have created a file where the users select the filter criteria with the help of a drop down and when the macro is run the data from the master file is filtered, on the basis of the drop down and is saved in another sheet. Then I have applied a pivot on this filtered data and there are a number of sheets which gets updated. These activities happen when you run the macro. Now i have a huge dataset i need to handle and i have no option but to use a powerpivot, but i am not sure how i can use the same logic in a data model in excel 2013. Could you please help me out?
Thank you Trevor. This is exactly what I was looking for. However, I'm having some issues with the VBA code and wonder if the CurrentRegion is not available in Excel 2010. When I get to the step of making the range dynamic, I do not get the drop menu of different codes like you do. Any suggestions?
Hi Mooonbug,
CurrentRegion works in 2010. Make sur you have a blank row above it and a blank column either side of it.
You need to check if the dynamic named range is working. Go to the Nmae Manager / Refers to:
There are several tutorials on creating named ranges on the website to give you. For testing you could use a static named range.
Best wishes
trev
Thx u 4 share this... Its mean alot 4 me
Hi Teja,
Happy to help.
Trev
Excellent! many many thanks!
Hey how I can search for a rang of data for the similarities from set of data. is there any cod for that?
Hi! I can not filter between two dates. Do I have to formate my sheet somehow to recognyse dates or what? Thank you. And by the way, you are awsem.
Thank you! You made my work easier. haha!
Great Explanation Thanks a lot !
Absolutely great! Thank you very much! Do you sell any advanced Excel non-VBA book? Because as silly as it may sounds, I know all I need from VBA, but I would like to know thigs as the ones you have shown in this video, like the usage of the software without programming in VBA.
Matias Franck
Hi Matias,
I do not have anything except what is on the website
www.onlinepclearning.com
Trev
thx, looks great !
Hi! It was a great tutorial but I am facing some problem regarding filtering the data. It works perfectly when I enter values in criteria but when put formulas in it it does not work. Hope you could help me figure out that
Every time I run the filter Macro the data will appear about 6 columns to the right and 2 rows up. It will clear just fine. Did I miss something in the formula that is making the filter show up in another location?
Sir small doubt
Can we retrieve this data to user form like this by drop down Search events.
If so please support.
Really helped me this video, by the way sir I want to see my last available row in filterd sheet for which I might want to scroll down,, is der any technique for that
Good job !!!, Thanks for sharing knowledge.
Appreciated
Trev
I tried doing this with other sets of data that did not include dates. My headers were correct, but it would not filter out my data. On your website, it included code where if you didnt have any dates selected the filter would work. I am not going add some random dates so that I can get my data to filter. Can you help me?
The same problem!
Maybe to add column "ID' and instead names to give numbers from 1 to 1000 for example?
Hi
This tutorial may be of help.
ruclips.net/video/0f_jlbJBB5Q/видео.html
Also make sure to run you filter manually from the ribbon before you record any VBA.
Best wishes
Trev
Thank you very match! I did it! =)
Hi Trevor, I followed everything you did which works fine. However, when changes was made to the code to call CurrentRegion, the code will not run. How can I fix this issue? Thank you
Thanks Sir, This tutorial really helped me.. thank you a lot.
Thanks Paresh for the positive feedback.
best wishes
Trev
Welcome...
thank you so much this tutorial was awesome however I would like to ask you a question.
what if we had more than one item in the cells for the category item? for example if you had in one of the cells travel, education.
I would appreciate it if you could reply to this.
thanks
Kasra Rostamkhany
Hi Kasra,
If you add * wild card operator either side of the criteria with the formula it should pick up "everything containing"
This should allow for 2 words or parts of 2 words.
Best wishes
Trev
Trevor Easton
Thank you for your reply Trevor, I tried this all day and have gone through everything I can but I cannot get the result I want.
what I have at the moment in the data sheet is that I have a column called Region in each cell there are several countries separated by a comma for example in each cell I have "UK,US, France and etc". I want to be able to search for France for example but get the result from the data sheet whether or not France is in the beginning, middle or at the end.
The problem is that at the moment the filter finds only the cells where France is in the beginning and if France is not the first item in the cell like: "Brazil, France" then the filter would not reconcile that and would only return the result with cells where France is in the beginning of a cell like: "France,Brazil"
I apologies for the long question but I would much appreciate it if you could help me as this is driving me crazy (:
Many thanks
Kasra
Kasra Rostamkhany
Hi Kasra,
You can use wildcards either side which will filter values containing.
Eg *France* or *Fra*
But the main problem here is your data structure. You should put the countries in separate columns or rows with unique column headers.
Best wishes
Trev
Hi Trevor,
thank you for responding to my question you have been so kind to try to help me; however, I cannot change the data structure as there can be many countries in each cell. Hence having so many filters makes no sense.
Just to explain the data, the rows are companies and the column is countries they work with.
Many thanks
Kasra
Hi, Trevor Easton... Good Job!
I have question, my advance filter is working fine except on numbers. ie Car Year Model.
Please tell me why filter can search text but not only number?
Thank You.
Hi Syed,
I am not sure if your car year are in separate columns.
Do you have text and numbers in different columns or text and numbers as a string in the same column.
Trev
so many thanksss!!
Hey Trevor! Thank you so much.
Could you please tell me how to access to your website? Apparently, it doesn't work.
That was really Great tutorial
Can u make the dropdown list dependable?
Hi. Nice job.
I am truing to place Criteria Range to another sheet and I am facing difficulty. Filter process does not run. Is it possible or they must be to the same sheet as destination.
Excellent video, Thanks ...Thanks ...Thanks .
I did the same thing but with different DATA. When I input "DATES" it won't filter anything but when I input a "CATEGORY" it works. Do you think is the formatting of the dates?
+Gilberto Cano I have the same issue. Did you get any answer?
Hey Trevor, i am having the same issue as Nick. Have tried everything i can think of, just will not work when the if statements for the dates are entered. Have tried Named Ranges, no Named Ranges, your formulas to a "T" even tried them as an array formula as my range is in a table. I have no idea!!!
Only the Data Validation on the text string or the date cells blank (with no formulas in them). Even when blank (as per the if statement), won't work.
Any further thoughts??
I have so many applications for this if i can get the first one to work!!
Dean.
hi Dean,
Impossible to tell from the information. I have used advanced filters with variable criteria on some very large data sets and it has worked fine.
I would suggest go back to basic. Run the filter manually (from the ribbon) with the different criteria sets. If it will not run from the ribbon then it will not work with VBA.
good luck
Trev
Great tutorial, thank you very much. There is one more issue would like to check with you: If my data include URL links, after filter, the links not working anymore. How can I keep the links after filtering?
*****
Hi Ch,
You can loop through the range and create links from the filtered data in the next column.
Like this (Alter range to suit)
Sub Macro2()
For Each c In Range("K1:K1000")
c.FormulaR1C1 = "=HYPERLINK(RC[-1])"
Next c
End Sub
best wishers
Trev
Trevor Easton
Thank you very much for your information, I am trying but cannot make it work due to my limited knowledge of VB. Can you please have a look of my following files to check how can recreate the link in filtered result? www.dropbox.com/s/zhwelx8n9uodf76/Filter.rar?dl=0
Thank you. Great video. Helps a lot.
Could you please explain how extract specified columns instead of extracting/filtering all of them ?
Thank you very much.
Kind Regards,
T.
Hi Tolga,
All you have to do is only add the columns you want in the Copyto Range. You can have specific columns only or all.
Best wishes
Trev
I love this.. Thank you
Happy to help
Trev
Trevor, how can I amend this so every time I run the code it adds to the next empty row? Every month I intend to use this method to pull the latest months data using a month column as my criteria range but don't want the previous data to clear.
Hi Paul,
I'm not exactly sure what you are referring to Paul. This tutorial shows how you can filter data in a database with certain criteria. I have attached the link below to a basic yet more comprehensive tutorial that shows how to filter data and also our data to a database.
www.onlinepclearning.com/excel-phone-book-contact-manager-excel-2010/
please accept my best wishes
trev
Awesome video as usual. I can't get the Dates to work. In the criteria boxes for the 2 dates, I am putting the code in exactly as yours, making changes to the cells that are in my spreadsheet, but for some reason, the filterme macro doesn't show any data from the table once i put code in the date criteria boxes. Any idea why? It's as if the boolean or "&" is throwing it off. My filter works with the other boxes where its only looking for strings.
Hi Nick,
Thanks for your comments.
There is an article on the website withe formulas to copy.
Sent me an email from the contact form and I will help.
My best wishes
Trev
dear mr. Easton,
Sir I have a problem with the soruce fields. In my source ı have cells that connected with formulas. such as : unit price*quantity=total price.
When generating the filtering (as gathering data according to my selection criteries), I getting error, and proccess fails. how can ı solve this issue.... deleting that formulas in my source, fixes the problem. however ı need those formulas.
Writing the formulas into the output fields doesnt much serve my aim. THANKS IN ADVANCE. Ati.
Please help me. What codes should I use for login form. I have already my login form with username (textbox), password (textbox) and login (command button). This is the case, I want to proceed to another sheet of excel everytime I logging in to the form. Please link me to another video if it is possible. Thank you.
I'm wondering why you don't use Power Query to do this work? Power Query seems to do a lot of the bulk of the work that Advanced Filters use. Of course the reason I'm asking is because I'm having difficulties with them :)
Love, love your videos, they're fantastic, great pace, clear voice, and nice background music 10 out of 10.
Trevor,
Is there a limit to how many Criteria can be in the Criteria Block?
I'm finding that your example is limited to 4 criteria - I can't get the Advanced Filter to go beyond this!
Hi Philip,
There is no limit but bear in mind that the criteria are mutually inclusive. ( eg. 2 sets of numbers or dates need specific attention)
Sometimes because of that you may need to run a couple of separate filters.
Best wishes
Trev
Trevor Easton Thanks for your reply.I'm guessing that the Criteria Block that you've shown, with all criteria on the one row, is a series of AND formulas. However, if a new row of criteria is added to the block then we have a series of OR formulas? This additional set of criteria on an additional row works better for me.
Thank you Trevor, very helpful but I need to filter more than two values. For example, i want to filter 2 tax categories and want all require data. Is that possible!
HI Rahul,
Yes you can filter with OR or AND.
Run some tests manually.
The criteria for AND is horizontal as in this tutorial.
OR is vertical.
eg
HEADER
Criteria 1
Criteria 2
etc
This is the OR filter setup
You can run AND and OR together
Best wishes
trev
+Trevor Easton Thank you so much for your prompt reply. Like me there may be several subscriber who might want to learn advance filter with more than one value. Is it possible to make a video on this topic? I appreciate your work, hence the request! :)
Trevor, thanks for your great sharing.
However, I have a question regarding the advanced filter and I would be really grateful if you could help me with that.
I have set up my own codes for the Advanced filter and I did NOT set the codes for the clear button, Then I notice that if I do not clear the content before I use the Advanced filter button on the same sheet again, the only changing cells will be only on column A (my copy to is A4).
at first I thought that it is the problem of my codes, but even if I manually go for the advanced filter and select the data range, criteria range etc, manually again, the only changing data is on column A and other data wont change.
I then conduct a further experiment to clear the data before I tried to use the advanced filter (both my VBA button and the manual way), they work fine.
May I know why? is there anyway to make the filter work without having to clear the content before i use it for the second time on the same sheet?
Thanks in advance!
Hi So,
You many need to run a line of code to clear the data as you run the macro.
Best wishes
Trev
Trevor Easton Hi Trevor, thanks for your prompt reply! I notice that I need to run a line of code to clear the data. without that clear, the vba just does not work.... Thank!
Thanks for posting this video as well! I would like to ask a further question.
I am now handling about 20 kind of economic data (eg. GDP inflation rate, unemployment rate) for 250 countries.
These massive data are all stored in a worksheet. So in this worksheet, there are around 5000 rows of data. May I know if there is any way that I can distribute the 20 economic data to 20 different worksheets? ie, one worksheet will only have GDP and the other only has inflation rate etc....
Thanks in advance! Have a good day!
thanks mate!
Great tutorial...love all your tutorials....learn a lot from them.........I have applied this particular tutorial to one of my excel databases. Everything works except for 1 criteria. The last criteria I use is whether or not an item has been rejected. If it has then in the database under the column REJECT, an "X" is put in the record.When I start filtering, it filters like I want except when you put an 'x' in the reject criteria.It will give only the rejected items the first time and all the other times with other criteria. But when you take the 'x' out of the criteria box, it still filters for rejected items, instead of all other records that are not rejects.Right now there are over 12,000 records and more added every day. The column that has the "x" for reject - some records has an 'x' and some don't. I use the formula =if(f4="","",f4) in the criteria range. What could be the problem? I can send you the workbook if you want.
Hi Donald,
X is text so if the criteria is blank you should put in the * to enclude all. I notice in your formula you have "" if blank.
This may help to solve your problem.
Trev
Hey, trev....I changed the formula like you suggested and when I filter now, it brings back no records at all. With or without any thing in the Reject criteria and even with another criteria added.
Hi Donald,
Can you show me the formula?
Trev
=if(F4="","*",F4)
wanted you to know that I figured out why this wasn't working for me.The "Reject" criteria column had "x"s in the cells to indicate rejected loads. All other cells in the column were blank to indicate that the loads were accepted. I used "yes " and "no" instead and it started working. I modified the formula a bit. Now the spreadsheet works beautifully.
What a legend
Thanks for the great video. Is it also possible to make the itempool you can select in one Dropdownlist dependend on the item you have selected in the Dropdownlist before? Want to let Excel check e.g. okay you have choosen a date in the dropdownlist and there is only one category in the datapool (data sheet) in subject to this date. So Excel should only give me the possibilty to select in the Dropdownlist "Category" this specific category item. Best wishes M
Hi Marcus,
This tutorial will show how to do this
www.onlinepclearning.com/excel-dependent-data-validation-cascading-data-validation/
Best wishes
Trev
Hi Sir, I followed your examples as shown on the video and it works well except for the dates. Wondering where can the problem be? Without dates it works fine. Are ther more examples for using dates for advanced filters.
Hi Peter,
The complete tutorial is at the link below.
www.onlinepclearning.com/vba-advanced-filter-multiple-criteria/
Best wishes
Trev
Thanks for the video. I noticed that its only possible to filter on the first part of the strings using this method. What if I would like to use any part of the string in the search? In your example it could be searching for 'ravel' instead of 'travel' in the tax category.
Hi Jesper,
I is common for operators to be used to change the way data is filtered. The wild card* is often used for this. Search operators for advanced filters in google.
Data that contains would be *text*.
Trev
Wild card either side so the text
Works as a charm , thanks. I have an additional question a little off topic that you might be able to help with. In my this list I have multiple records with the same info except for the date. Would it be possible to include in the filter only to give me the record with the newest date?
Jesper Glar Nielsen H Jesper,
Use the Max function in a formula to show the latest date and use this as the criteria. Add it with some VBA.
Trev
Date range is not working. Please help. When I'm putting the formula based on what you have written the filter is not working. Im stucked with it since yesterday. Thanks
How do i get it to filter part of a number like if i know the last 4 digits but not the first 3
great very useful thank you
Superb sir thank u so much
Hi Trevor. I find this tutorial very helpful. However, can you help me how to make criteria with at least 5 rows. I tried to edit your code and adjust the criteria. However, it doesn't seem that its getting the right value that i want. Hope you can help
Hi Glicel,
You can run criteria in rows. This will give you OR as the overator.
For get the code at first. Work from the ribbon (Advanced Filter)and run it manually.
When you have it working then record your code.
Trev
Trevor Easton Hi Trevor! Thank you for your help. But what I did was I added a new button and add the code and adjust the criteria. Thanks a lot!
How do I create a user form that accepts multi search criteria from the user and displays the search result on the user form without the user seeing the excel data
When I add the 'FilterMe' macro to the button it says 'the exact range has a missing or illegal field name' though I followed the tutorial exactly step by step. Whats going on?
Hi Mandy,
Check that you have named the criteria headers exactly the same as in the data. Headers must be included and exactly the same in the Extract/ Criteria and Copy to sections for the advanced filter to work.
Trev
Hi, how to filter excel table just by entering a part of number in the heder(cell value), please code here
Thanks so much, you are touching so much life's... Wanted to know if you could pick the data from multiple sheet that will be of great help
+Divine Awudi
No that is not possible
Either combine the data or run multiple advanced filter code.
Trev
Thanks 👍🏼
For some reason if I leave the date field blank the advanced filter will not work. I am using the following formula for the start and end date: Start =IF(C5="","",">="&C5); End =IF(D5="","","