Not typically one to spend my time commenting on things on TH-cam but this was a really great video and as someone with basic programming knowledge easily walked me through the steps of this useful tool so I could rework/implement it on my spreadsheet. Thank you much for the help!
I always wanted to learn VBA but every tutorial I found along the way, including books, scared me and I gave up. I saw one of your videos by accident and now I'm trying to watch them all. VBA still hard to understand, but you have a sweet way to put it on your videos and I'm glad I found you. Thank you very much for sharing.
Just found your channel, it's great. One suggestion (which you may have passed on for a valid reason) for this video would be to use ternary operation instead of If->Then->ElseIf->Else=>End. Example: ConName = IIf(.Range("E5").Value = "Enter Name", Empty, .Range("E5").Value)
Thanks so much, I do appreciate the suggestion. I will try to use that in the future. That condenses and streamlines the code much better. I really appreciate your feedback. Your expertise would be extremely helpful in our Facebook Group so please consider joining and helping out here: bit.ly/groupexcel
For sure, you are very welcome and thanks so much You may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Discount
Hi and thanks very much. If the data filter is not working it may have to do with your regional settings. Make sure that the date formats are all the same as your system date formats. I hope this helps and thanks so much.
Thank you very much. I do appreciate that Kerlos. Check out the new touchscreen POS video I released yesterday here: th-cam.com/video/1xyvioUP8Ls/w-d-xo.html
Hi and thanks so much. You can download this file using the links in the description above with either your email or Facebook Messenger. I hope this helps and thanks.
Sir i love your channel. Kindly make video on google sheet. We have multiple user editor or viewer issues. If you can help us in that. Thanks and best regards
Hi and thanks so much. My channel focuses on Excel-based applications. I do have some videos that sync with Google sheets. Thank you for your Likes, Shares & Comments. It really helps.
In routine work it is most important to find the values form the dataset. This is the best tutorial I have seen as of now.. Keep doing the great work. You are helping too many.
Nice tutorial. Have a quick question, in this when you typing any thing it is filtering that data and selecting the filtered rows. Any way that it doesn't select those filtered rows? The issue suppose you want to use multiple filters then after applying first you have to click somewhere to unselect and then again. Thanks
HI and thanks I am not sure I understand your question, however what we can do is have VBA select another cell right before the macro ends, such as Sheet1.Range("A1").select This way the user does not have to select another cell and they can select the same cell over and over again. I hope this helps and thanks so much.
Thanks so much. I try to do something very different, hopefully that people have not seen. Also feel free to join our facebook group here bit.ly/groupexcel and facebook page here facebook.com/ExcelForFreelancers/ Much appreciated
A million thanks for this excellent tutorial. I kindly want to ask, How do you clear the search field upon clicking? I always had to delete the "Enter the name" before I can type in what I intend to search. How do set the search field in such a way that when the user intends to type into it, the default text( i.e "Enter the name) clears automatically without having to delete the default text manually?
Hi and thanks so much. We can have VBA do the clearing with a Selection change event, something like if Target.value = "Enter Name" then Target.clearcontents Something like that. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
thank you freelancer, is there any help forum to solve the issues regarding vba-excel , so i can send my project to get solution Way of interpret is very very good in your video..
Hi Muhammad, thanks for your comment. Please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Sir thanks for this productive video, it's really helping me. But one challenge I get is when I sort the table based on particular column the filter fields move into the data body. eg. Content of Row 5 is displace to another row
HI Erasmus, thanks so much for your message. If your filtering headers are getting caught in the sort then you would want to change the code for one row down (So row 5 instead of row 6) You may have to adjust your other code as well. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hi Mr Randy. What do you think about setting application.enableevents to false instead of setting true or false to prevent the worksheet event to trigger when changes is made to certain cells?
Hi and thanks. Yes that works fine as well, however sometimes I want to know if its loading or not for other reasons so the True/False option can help with that. I am using Enable/Disable Events a lot in our School Management series in which I show you how you can create your own Excel applications, from scratch, in an amazing series right here: bit.ly/SchoolManagerSeries
Hi, thank you for making and sharing such great video...I am new to learning VBA and would like to know the changes that i need to make if I have to filter the zip code (any data that only has numerical value) in this file
Hi Dhaval, thanks for your comment. Numerical values can work on an exact match if you remove the "*" both before and after. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Good evening, thank you for you tutorial,this helped a lot. Got a small question for you if I may. When you filter or clear the sheet you end up with a selection of what you filter of clear. Is it possible not see the selection? Now I have to click somewhere to unselect it. Sound basically simple I guess, but how do I do this?
There are many ways to filter a numeric value. You can first add in data validation for the cell in which you are adding in the criteria. You may also want to run an isnumeric formula to check. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Phone #'s should be treated as a text file so make sure that all the field formats are either General or Text and also make sure to add the "*" character before and after the search text through vba. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hi Sir Thank you so much for your tutorial, it help me so much in my work. Your knowledge on vba is extraordinary. I got a question on the clear filter, when i try to clear the filter on my worksheet, it only clear the value entered in the input column .. but the clear filter function does't return the data/row to usual.
HI i am really glad you like the tutorials. You will want to first make sure that you are running this filter NOT on your original data. This way when you want to clear the filter, you just have to clear the table below and bring back your original data (probably from another sheet) and copy it into the table. The key is that what the user sees here in this filtered table is not the original data table.
Thank you so much for sharing your knowledge. Learned very much and have also apply it on my table. I have a problem with filtering the colum on account no (numbers). Is there a solution for? Thanks in advance.
HI thanks for your comment and glad you liked the video. I have created a Facebook group with 4700 Excel Exerts so that many of us can help you with your Excel questions. Can you please post your detailed question, with samples or screenshots directly in our Facebook group? If you have not Joined yet, please join here: bit.ly/groupexcel. This way we can get you the answers you need, fast.
Thank you so much, it's a great video. Thank you for sharing I have one question. Can we make an auto filter with drop down list ? I hope to find an answer for my question Best regards
Hi Mohamed, and thanks for your comment. Yes you can use data validation as well with this. Instead of a free text field, just use your filter field with data validation list, then it becomes a drop down list. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Sir really your videos or mind-blowing, but I dont know VBA code… your concept was always great…. I never miss your videos sir Sir, if possible do one video for Spare parts Stock like Stock List, Purchase, Sale and Graph sir
@@ExcelForFreelancers Sir How can I insert Mobile no. Filter and Invoice no. .. bcoz u told 3 text format and 2 date format like Dim as string and date… I dont no the VBA code sir Sir if possible need insert 2 to 3 column and VBA for filtering sir 1. invoice No. 2. Mobile No. 3. Bill No Its very help ful me sir Need code updation sir …. Its request sir I am very happy this video sir
Excellent work , but I think before watching your videos i need to learn atleast basics of vba, Should i go with that free course of yours or any you tube video for that????
HI and thanks. Yes it is a good idea to have some VBA fundamentals first. I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
Would this work if we have several worksheets ( tables) with different data, like Customers, Invoices, Invoice Items,......... Do we have to create an individual Filtering Modules for each sheet/table? How to differentiate/name them? Please advise.
Hi and thanks for your comment Moe, The best way to do this is to hide the original database for each sheet. Then create a new 'List' Sheet for each database. You can then filter & sort these sheets however you want. So you would have a separate sheet for Customer List, Invoice List, etc.
Thank you this is very useful, I have adjusted it to show a list of my movies. I have one problem, I want to protect the work sheet so the data can't be altered but when I protect it I get Error: Selection.AutoFilter . It works perfect until I protect the sheet. Been scratching my head for days..
Hi and thanks for your comment, When protecting your sheet you can select the 'Use Autofilter' option which will allow users to use that. Also through the vba code you can add the xlUserInterfaceOnly command which will allow VBA to make changes that users cannot. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Excellent tips regarding the flashing reaction. I have one question in regards to the macro "Tablefilt" how come the "If" is not expecting an "end If"? Am I missing something?
Chuck, not only you, but I am learning every day as well. I think I have learned more in Excel this last year while teaching it than I did 5 years before. We are ALL students, so I am learning right along with you. The idea is if you want just 1 thing to happen after an IF, then the End If is not required, however if you want multiple things to happen after an If then you will want to enclose them with an End If. I hope this helps.
Hi and thanks you can apply this on any table at all, or just use a range. both should work fine. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers I have created an advance filter macro for same kind of application and it wotks well, but the issue is any row with a blank cell (any of the criteria) is not getting filtered. Can you please help me sort it out?
HI Asif, can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 30,000 Members who would love to help you with this. Thanks so much.
Hi Randy, Thanks so much for all the examples you have online. I don't know any VBA, but have been able to put quite an elaborate file together for work. As usual, I've modified your example here. I have two criteria for my filter to be empty...If .Range("A8").Value = "J" or "A" Then RName = Empty Else: RName = .Range("A8").Value 'Rep Name . My question is, how do I enter the J and A so it reads both? Thanks so much.
Hi sharing i am glad you like the videos. For using Or within the code you can write If .Range("A8").value = "J" or .Range("A8").value = "A" Then RName = Empty Else: RName = .Range("A8").value I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Works better adding a change event in the fields, added like an activex control, so that the data is filtered as it is written and we don't have to press enter. Just a suggestion. Thanks.
Hi thanks that is a good suggestion as well, however Active X does not always work well with Mac's and with large tables adding so many controls may be a bit slow however on a smaller table it's a very nice idea. Thanks for your suggestion.
So if i would want to filter without pressing enter, how would this be done? Excel For Freelancers, Thank you for all the videos that help me very much.
HI Ute, thanks for your message. While I don't personally have experience with Greek characters, make sure you change the fonts in the VBA Editor and your computer as well (You may need to restart after this) I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thank you very much for this very helpful tutorial. I want to know how to develop this more. Suppose I want to filter by "Name" column by entering more than one word(2 words or more), how can I do this
HI Jamal, thanks for the great question. Yes its possible using OR in advanced filters. First you would have to separate the two words in VBA. Once separated Using the same Criteria in different columns in the Advanced filter will provide the OR function. You can see my training on Advanced filters to see how the OR would work here: th-cam.com/video/YuCrEWU2Tm0/w-d-xo.html
@@ExcelForFreelancers what I want is to get the cells in the "Name" column that contains the two words (or more). So I guess we need AND not OR what do you think. If yes how it can be done?
Hi Thanks so much for your comment. I did just make an update to the Facebook Messenger link which should be working well for downloading the workbooks. (Email link for download should also be working) Please let me know if you have any questions.
Hi I am very new to Excel VBA. I like your Table Header Filter and it works well for me. I, however, have a question. I have a column (ID No/Social Security Number). column. How can I modify the code so that I can also search on a field with numbers? Thanx
Hi you would want to make sure that the formats and the variables are set to numbers so that it ensures excel uses #'s. Also make sure to remove the "*" from before and after the search filter variable. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
awesome video. I just want to know that I want to use this filter in Excel in moible but mobile excel does not support VBA. So is there any other filter which i can use in mobile to search data regarding a customer such as mobile number, address etc.
Hi Excel is not very powerful in mobile so maybe you would want to use Google sheets when it comes to mobile (because of the lack of VBA in Excel in mobile)
I'm new into VBA and starting from scratch beginning with your first videos and I would like to thank you for these videos. when I applied this tutorial step by step, the macro didn't show any error but the filter hided the whole results in the table and didn't show any result (when trying the tutorial excel file that i downloaded it also didn't show any result). I tried mm/dd/yyyy and dd/mm/yyyy formats with no success, any idea what might went wrong?!
Hi and thanks for your comment. When it comes to using dates in criteria its better to link to the source (and not have VBA add the criteria) The date should not be formatted as a date and should be in number format. So something like=C5 (so it would appear like 45100 for today's date. or something like +">="&C5 so it would appear >=45100 I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi Can you suggest me any video by which after filtering data, filter data can be extracted to other place and Depending on the filter information, everytime time the extract data will be changed. Wishing for your suggestion
First of all, Thank you so much for the video.. In above video suppose we type 'John' and it filters out and shows the result,. In the filtered out result can we update/manipulate data such as changing contact info so that it gets updated. Is it possible to do so?? If yes please let us know. Thanks. 😊
HI I am glad you like the video. In the workbook above, this sample you can update easily by changing the data at any time. You can then re-run the filter again if you like. Make sure you download the free workbook (link in the description) and try it out for yourself.
Hi Randy. Really enjoying the learning development and experiences gained from your tutorials and use of downloaded workbooks. Unfortunately I have hit a snag with the use of "Selection.Autofilter". When I run the code, I am immediately returning the error, sub or function not defined, high lighting "Auotfilter" . Here is a section of the relevant code which I hope will be enough for your to help identify where I might have gone wrong: Selection.AutoFilter With .Range("C5:X" & lastrow) AutoFilter field = 4, Criteria1:=">=" & TDate, Operator:=xlAnd, Criteria2:="=" & BDate, Operator:=xlAnd, Criteria2:="=" & SPlan, Operator:=xlAnd, Criteria2:="=" & RDate, Operator:=xlAnd, Criteria2:="=" & NextAss, Operator:=xlAnd, Criteria2:="
Hello Stephen, Try to clear the filter before running a new filter, and also check your protection settings. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
HI and thanks I also got your email with that question and responded: I have some great courses on my partners page to learn the fundamentals here: www.excelforfreelancers.com/partner-courses/ Thanks so much.
Hi Randi, I was wondering if you could list in your web site the list of the Excel workbook you created in order to download easily as each time one wishes to download a workbook one has to go and re-enter the user id and password. This is just a suggestion. Regards, Chuck
I agree with you, Chuck, it's kind of a pain, so I am developing a single workbook library with my most popular applications all in one. This should be a really great and very fairly priced application so you can have a complete VBA Application Library at your fingertips.
Randy I need your help can you let me know how to filter using numbers for this worksheet I was reading some of the comments and I know they are old but some other people had the same issues if you can just help us out.
Hi Roland, you will want to make sure to format all columns as numbers, and make sure you do not add an asterisk before or after the numbers, so they are treated as numbers, not strings. I hope this helps and thanks so much.
Hello, Firstly, thank you for producing these videos. I am learning a lot and it is supplementing my further readings on VBA/Excel. This is a personal hobby and not my day job. My question: I love the filter in the header and learned quickly that the data can't actually be in a Table w/header (unless the data is in a table without the headers and you created the headers separately?) Anyways, I have a column of data that is conditionally formatted. The header is "Complete" with a simple Y or N to trigger the CF. IF yes, nothing happens; however, if N then the square fills with a light red. Very basic. I have created a filter for this column and it works for Y (unformatted) but not for the N (which has the fill/format). I would like the filter to also work for the "N". Do you have any suggestions without removing my Conditional Formatting? Thank you again for your awesome Videos! Dave
Hi Dave, sorry i missed you original question. I am not sure I understand the issue entirely. If you have entered both Y and N as text in the table, the filter should work properly and you should also be able to assign regular or conditional formatting to either of these without issue. Perhaps you can upload the application or screenshots in our Excel For Freelancers Facebook Group so we can take a closer look at it. I know we will find a solution for you: bit.ly/groupexcel
Is it possible to filter data by one column topic OR the other? I mean, there are times I don't know whether a piece of data appear in column F or G, so can I set the code to look for it in either one but not BOTH altogether? So the table returns me either rows with said data in column F, but also rows with said data in column G?
Hi you can use Advanced filters in VBA to check for both results, one at a time and then post all of the results. I will try to make a video on this within the next few months, however please feel free to ask this question in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel we have 23,000 developers who would love to help you with this.
Hi Thanks for sharing knowledge. I have tried your VBA for my data. While everything is working fine, date filters are not working. I am working on an dd/mm/yy format in my excel file. Does it make any difference? Can you please help?
Hi and thanks very much. When it comes to date filters, use a 'General' format in the criteria and not any other formats. This should filter the date properly. You an also use a formula in your criteria directly to the source of the date. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hello sir than you for this, I have tried it and it works.. only problem is when i tried typing a name not included in the selection and did it twice, the other rows automatically hides and the filter moves up unless i again clear the selection, it then goes back to normal not hiding the other rows. pls help
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 50,000 Members who would love to help you with this. Thanks so much.
Very helpful, thank you. In some cases of I don't clear the filter before I change the values, it applies the filter to the header and it flips the input field with the header. Any idea why?
Hi Francesco, It is important to clear both the Criteria and Results before adding new criteria then running the advanced filter. Also you should often Delete the criteria before a new advanced filter (If you run an advanced filter with no criteria, in other words you just want unique lists. Excel 'remembers' the old criteria, so its important to delete the named range "Criteria" before applying a new Advanced filter. This can be done via VBA. You can find some examples of how i Delete this in this video here: th-cam.com/video/kiQ5a_iJUcQ/w-d-xo.html
Hi Thank you very much for the tutorial. This helps me a lot to solve a problem fast. But I have one question.#Question: After doing all the steps, I can easily filter according to my need. But it is only for once. I mean this micro is not saved. When I open my file again, and try to filter, i Cannot do it. I have to write the code again and they it works. So any way to save it to my file. So that it becomes completely auto.
Hi thanks very much. You can save your filters in another table in order to recall them. It's best to separate your actual data in one sheet and have the filtered data on another sheet. This way when you re-open your workbook, nothing changes. Here is another video with some Advanced filtering: th-cam.com/video/kiQ5a_iJUcQ/w-d-xo.html I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Hi FOR TEXT TYPR, you use them as string For example, Dim Contname As String So, what i will use if I have number to search, like no text, it a postcode such as 1200, 12300, etc. So how i will address them?
sajeda mony whole numbers can be Dim as Long. Just don't use the "*" at the beginning and end of the variable when filtering. I hope this helps should you have any additional questions please join our Excel for Freelancers Group where we have over 8,000 Excel professionals who can answer your questions. bit.ly/groupexcel
Hi you would want to use an ActiveX component instead of a cell-based component if you want to filter results based on what you type. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thanks, its very helpful. I do have a problem. I changed the name column into numbers and the filter wouldnt work, can you please help me in making the autofilter to work with numbers? Thanks,
Hi thanks for your comment and I am glad you find it helpful. You will want to make sure your criteria is using the same header name as your original data within the advanced filters. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thank you sir so much for this tutorial I have a question about the format of date because i want to lunch search with this format ("dd-mm-yyy") what i need to do to change the date format in your code Sorry my english is not so good ^^
Hi when it comes to date formats you want to make sure to check 3 things: 1) Your computer has the right date formating in Windows or Mac 2) The cell has the same date formatting 3) In the VBA Code you can format any date using this code "Format(FrDate,"dd-mm-yy")" I hope that helps. Please feel free to join our group here for quick and fast help: bit.ly/groupexcel
Hi it may be able to be done with an activex or form control field as those types of boxes have events on change, while cells have macros that run only when a user exits a cell. I believe their are some videos on TH-cam describing this so a quick search may be helpful. Thanks very much
Hi. I know I'm late to the party, i just want to check, in the last section of the video. Do you still hit enter to bring up the search results? I know it's meant to be automatic but even with the coding mine does not bring up the results by itself. My data is grouped. Would this influence this function?
Hi and thanks make sure the change event is being activated and also feel free to step through the code using F8 to check for exits to the code. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi Bharat, you can add a data validation in your filter along with a auto-complete to help users. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi Sandeep, thanks for your comment. You can try protection in VBA with the setting xlUserInterfaceOnly, otherwise you can unprotected it and reprotect it within the code. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Instead of putting the Clear Filter with True/False in a cell on the spreadsheet, could you have also just defined a variable above the first Sub in the module?
HI Scott. thanks for your comment. We want to make sure we differentiate between two types of worksheet changes. One in which the user is making a change when they want to filter, and the other change by VBA when clearing filters. Setting a True/Values value in a cell helps us differentiate between these to changes when triggering Worksheet_Change events. There may be several ways to accomplish this. Thanks for the great questions.
Hi, Thank you for this great video. Just want to ask for the number data. If the input value is not on the range, how to filter nearest higher or nearest lower number? Is this possible? Thank you.
Hi use can use Greater than > or Less then < to filter out numerical data on advanced filters. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hi! I did you a consult about a search/filter box in another video and here I found just what I wanted!!! But I applied it to my Contact Manager spreadsheet, and now some of the original functions don't work! The info of the contacts is not loaded into the respective fields when I click on one, and the color bar doesn't shows up either. Help me! What did I screw up?
Hi Gabriel, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
Hi, Could you please do a video on how to create a "SAVE DATA" button. Would like two things to happen when the button is pressed: 1 the data is save in the same workbook (in let say sheet 2) under the respective heading.and you get a message saying"DATA SAVED" and all all cells that have entire in it are cleared. Some cells having drop down. Would be a great help on this work book I am currently working on. Can send it to you and discuss more on it. Thanks, Regards, Marcus
Just one more thing, I want it to save the data no matter how many times the same data is typed. Example if I have a customer who comes In X number of times want X number of times the data saved.
Hi Marcus, thanks very much. I have been covering a lot of that in our Employee Manager series which I think you will find really helpful. There are tons of features here which will help you a lot to learn on Saving Data and all of the options that go with it. Its a multi-part series, so far 3 parts, but a new part comes out every Tuesday. This is what I have so far: Part 1: th-cam.com/video/iyr9WIyxlJc/w-d-xo.html Part 2: th-cam.com/video/lba5z5RlzxI/w-d-xo.html Part 3: th-cam.com/video/5UTLbi4W9aw/w-d-xo.html I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Love the program. I have 2 search fields A5 and C5, ContName (dim as String) assigned to A5 and Facility (dim as string) assigned to C5. I get no errors, however only field A5 (Field 1, Cell A5 ) searches correctly. C5 (Field 3 Cell C5) continually searches correctly but displays no data. Is the following code correct? If ContName Empty Then .AutoFilter Field:=1, Criteria1:="=*" & ContName & "*" If Facility Empty Then .AutoFilter Field:=3, Criteria1:="=*" & Facility & "*"
Hi Rhonda, Thanks for your comment. I would use F8 to step through the code line by line. To make sure the values are correct. Also make sure the values are also formatted as text in your table. If it still does not work, we can probably fix this for you. Please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
HI and thanks this tutorial teaches you how to do that via Advanced filter in which you can filter in place or another location. you will want to specify filters based on a number or text and then make sure the fields are formatted properly. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
I am new to VBA, one thing I'm curious is how does the Dim statement recognize the Name field as ContName and Address as ContAdd. Like can you just simply key in a name in the module form by keying in Dim followed by whatever name you want and "As String" or "As Date". I'm not sure how does it all works. Also, I'm creating a similar spreadsheet but I have more fields (some are numbers and some are texts). Do I need to make more Dim statement s and write in the module = "Dim (whatever name I want to refer to another field I want). As String"?
Hi it depends how you want the variable used. For example if you want to use a variable in math or find the maximum you would want to dim it as long or double (which are numbers) and if you want to format a variable as a date then you would want to Dim it as a date. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hello, i have problem, it cant search when i type name in Enter Name field. i run program but only appear pop up (thing) and i can search there when i click on pop box and tipe in search box, but it cant search in field above. Any solutions ? Thanx, you help me a lot
Hi Nidzo. We would have to see screenshots of your code and workbook to understand the issue. Can you join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel and please post your detailed question along with all of the screenshots. We have 25,000 members in the group, ready to help you.
😱 𝗨𝗡𝗕𝗘𝗟𝗜𝗘𝗩𝗔𝗕𝗟𝗘 𝟲𝟱% 𝗢𝗙𝗙 𝗧𝗛𝗜𝗦 𝗕𝗟𝗔𝗖𝗞 𝗙𝗥𝗜𝗗𝗔𝗬! 👉 www.excelforfreelancers.com/BFSale2024_YTPinnedComm
I´ve said it before, impressive work. Nice to here "why" that code is there and "what" is doing, perfect..
For sure, you are very welcome Michael and glad you enjoyed the training.
Not typically one to spend my time commenting on things on TH-cam but this was a really great video and as someone with basic programming knowledge easily walked me through the steps of this useful tool so I could rework/implement it on my spreadsheet. Thank you much for the help!
For sure, you are very welcome and thanks so much for your comment. Much appreciated
I always wanted to learn VBA but every tutorial I found along the way, including books, scared me and I gave up. I saw one of your videos by accident and now I'm trying to watch them all. VBA still hard to understand, but you have a sweet way to put it on your videos and I'm glad I found you. Thank you very much for sharing.
Wow what a great comment. Thanks so much. I REALLY appreciate that. :)
Just found your channel, it's great. One suggestion (which you may have passed on for a valid reason) for this video would be to use ternary operation instead of If->Then->ElseIf->Else=>End. Example: ConName = IIf(.Range("E5").Value = "Enter Name", Empty, .Range("E5").Value)
Thanks so much, I do appreciate the suggestion. I will try to use that in the future. That condenses and streamlines the code much better. I really appreciate your feedback. Your expertise would be extremely helpful in our Facebook Group so please consider joining and helping out here: bit.ly/groupexcel
I am very much interested to follow your videos. I am willing to study this VBA. Hoping I learned a lot. Thank you very much
For sure, you are very welcome and thanks so much
You may want to consider enrolling in Daniel Strong’s Ultimate Excel VBA 30-hour course, in which I have secured a special discount for Excel For Freelancer Followers right here: bit.ly/VBACourse_Discount
Cool and useful idea, thanks a lot Randy!!!! Only the date filter is not working...even in your sheet. Any fixes? Thanks and cheers, Chris
Hi and thanks very much. If the data filter is not working it may have to do with your regional settings. Make sure that the date formats are all the same as your system date formats. I hope this helps and thanks so much.
sir,you are best of the best.really i like all your tutorial .thanks
Thank you very much. I do appreciate that Kerlos. Check out the new touchscreen POS video I released yesterday here: th-cam.com/video/1xyvioUP8Ls/w-d-xo.html
Very easy and excellent tutorials thanks a lot👍👍👌👌
For sure, you are very welcome. Thank you for your Likes, Shares & Comments. It really helps.
Hi, thanks for sharing so useful! I've subscribed. Is this workbook available for download?
Hi and thanks so much. You can download this file using the links in the description above with either your email or Facebook Messenger. I hope this helps and thanks.
Tnank you from Greece George.Very Good work.
Thanks so much George, I am glad you liked it.
Thank you sir very useful videos👌👌👌👌👌 frm india
Most welcome! I am happy to help.
My journey of learning with you has been indeed satisfying and very much enjoyable. Thank you so much, Randy, from the bottom of my heart!
For sure, you are very welcome Chan I am happy to help
Sir i love your channel. Kindly make video on google sheet. We have multiple user editor or viewer issues. If you can help us in that. Thanks and best regards
Hi and thanks so much. My channel focuses on Excel-based applications. I do have some videos that sync with Google sheets. Thank you for your Likes, Shares & Comments. It really helps.
In routine work it is most important to find the values form the dataset. This is the best tutorial I have seen as of now.. Keep doing the great work. You are helping too many.
Hi thanks so much I really appreciate that. :)
Nice tutorial. Have a quick question, in this when you typing any thing it is filtering that data and selecting the filtered rows. Any way that it doesn't select those filtered rows? The issue suppose you want to use multiple filters then after applying first you have to click somewhere to unselect and then again. Thanks
HI and thanks I am not sure I understand your question, however what we can do is have VBA select another cell right before the macro ends, such as
Sheet1.Range("A1").select
This way the user does not have to select another cell and they can select the same cell over and over again.
I hope this helps and thanks so much.
Hi Randy. Very helpful. I have applied this on an excel project and it’s amazing. When done I will share. Many Thanks for your help.
Thanks so much Belibi, I am so glad you enjoyed the training and look forward to seeing the applications and solutions you will be creating.
Your work is exceptional. I love it so much. Thanks for sharing.
Thank you Syed, very kind :)
I liked so much your tutorials. I started to check them one by one, very useful, each one can show you at least a few new things. Beautiful!
Thanks so much. I try to do something very different, hopefully that people have not seen. Also feel free to join our facebook group here bit.ly/groupexcel and facebook page here facebook.com/ExcelForFreelancers/
Much appreciated
This actually wanted from months thank you
Hi and thanks I am really glad I can help. Thank you for your Likes, Shares & Comments. It really helps.
Dear Sir, I say from Bangladesh, After learn Basic Excel From your Tutorial, Then contact to you & learn your secret system. Insha-allah.
Keep it up, and thanks very much.
Thank you so much Excel For Freelancers . I really enjoyed the videos and learned a lot. Thanks again.
For sure, you are very welcome Sayyad.
A million thanks for this excellent tutorial.
I kindly want to ask, How do you clear the search field upon clicking? I always had to delete the "Enter the name" before I can type in what I intend to search.
How do set the search field in such a way that when the user intends to type into it, the default text( i.e "Enter the name) clears automatically without having to delete the default text manually?
Hi and thanks so much. We can have VBA do the clearing with a Selection change event, something like if Target.value = "Enter Name" then Target.clearcontents
Something like that. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you so much for this tutorial. I enjoyed this vedio.
For sure, you are very welcome Sonjoy
Excellent and Thank you for sharing this Idea....
For sure, you are very welcome Asim
thank you freelancer, is there any help forum to solve the issues regarding vba-excel , so i can send my project to get solution
Way of interpret is very very good in your video..
Hi Muhammad, thanks for your comment. Please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Nice training!!!, Thanks!!!!!
Thank you so very much, I really appreciate that.
Sir thanks for this productive video, it's really helping me. But one challenge I get is when I sort the table based on particular column the filter fields move into the data body. eg. Content of Row 5 is displace to another row
HI Erasmus, thanks so much for your message. If your filtering headers are getting caught in the sort then you would want to change the code for one row down (So row 5 instead of row 6) You may have to adjust your other code as well. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hi Mr Randy. What do you think about setting application.enableevents to false instead of setting true or false to prevent the worksheet event to trigger when changes is made to certain cells?
Hi and thanks. Yes that works fine as well, however sometimes I want to know if its loading or not for other reasons so the True/False option can help with that. I am using Enable/Disable Events a lot in our School Management series in which I show you how you can create your own Excel applications, from scratch, in an amazing series right here: bit.ly/SchoolManagerSeries
Hi, thank you for making and sharing such great video...I am new to learning VBA and would like to know the changes that i need to make if I have to filter the zip code (any data that only has numerical value) in this file
Hi Dhaval, thanks for your comment. Numerical values can work on an exact match if you remove the "*" both before and after. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
@@ExcelForFreelancers Is there a way to find auto-filter for non-exact match?
Another great video and spreadsheet. I just found this channel last night and I’m hooked! Thanks for sharing!
Thanks so much Don, very glad you are enjoying the videos. I try to make sure they are unique, Thanks again, Randy
Good evening, thank you for you tutorial,this helped a lot. Got a small question for you if I may. When you filter or clear the sheet you end up with a selection of what you filter of clear. Is it possible not see the selection? Now I have to click somewhere to unselect it. Sound basically simple I guess, but how do I do this?
Hi thanks for your comment. You can select any other cell in VBA to clear this such as
Sheet1.Range("A1").Select
I hope this helps and thanks so much.
Thank you sir for sharing the worksheet and it's also working well. I have only one question, there is any code/command to filter numeric value ?
There are many ways to filter a numeric value. You can first add in data validation for the cell in which you are adding in the criteria. You may also want to run an isnumeric formula to check.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Great video. How would I have to edit the VBA code to accept a search for phone numbers? Thank you.
Phone #'s should be treated as a text file so make sure that all the field formats are either General or Text and also make sure to add the "*" character before and after the search text through vba. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Excel For Freelancers Much appreciated. Thank you.
Hi Sir Thank you so much for your tutorial, it help me so much in my work. Your knowledge on vba is extraordinary. I got a question on the clear filter, when i try to clear the filter on my worksheet, it only clear the value entered in the input column .. but the clear filter function does't return the data/row to usual.
HI i am really glad you like the tutorials. You will want to first make sure that you are running this filter NOT on your original data. This way when you want to clear the filter, you just have to clear the table below and bring back your original data (probably from another sheet) and copy it into the table. The key is that what the user sees here in this filtered table is not the original data table.
Thank you so much for sharing your knowledge. Learned very much and have also apply it on my table. I have a problem with filtering the colum on account no (numbers). Is there a solution for? Thanks in advance.
HI thanks for your comment and glad you liked the video. I have created a Facebook group with 4700 Excel Exerts so that many of us can help you with your Excel questions. Can you please post your detailed question, with samples or screenshots directly in our Facebook group? If you have not Joined yet, please join here: bit.ly/groupexcel. This way we can get you the answers you need, fast.
Sir really wonderful teaching gr8 sir love you
Thanks so much, i do appreciate that.
Thank you so much, it's a great video.
Thank you for sharing
I have one question. Can we make an auto filter with drop down list ?
I hope to find an answer for my question
Best regards
Hi Mohamed, and thanks for your comment. Yes you can use data validation as well with this. Instead of a free text field, just use your filter field with data validation list, then it becomes a drop down list. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
@@ExcelForFreelancers very good ....... thanks for reply. I will try that . And Thanks a lot again.
Do your best excel for freelancers
Sir realy great concept
Can we use same method to other filter also like … Location filter, City like
HI and thanks yes certainly you can use this with multiple filters at the same time.
I hope this helps and thanks so much.
Sir really your videos or mind-blowing, but I dont know VBA code… your concept was always great….
I never miss your videos sir
Sir, if possible do one video for Spare parts Stock like
Stock List, Purchase, Sale and Graph sir
Hi and thanks so much I have that for Inventory here: th-cam.com/video/hagIuUPDCq0/w-d-xo.html
I hope this helps and thanks so much.
@@ExcelForFreelancers
Sir How can I insert Mobile no. Filter and Invoice no. .. bcoz u told 3 text format and 2 date format like Dim as string and date… I dont no the VBA code sir
Sir if possible need insert 2 to 3 column and VBA for filtering sir
1. invoice No.
2. Mobile No.
3. Bill No
Its very help ful me sir
Need code updation sir …. Its request sir
I am very happy this video sir
Sir after download this sheet from date and to date are not working sir…
Excellent tutorial. I have learned a lot of things today. Thank You.
Thank you very much. I do appreciate that.
Thank you Randi for your prompt reply. As we are approaching Thanks giving, I would like to wish Happy Turkey day! :) All the best.
Thanks so much Chuck, Much appreciated. I will have to get a big Turkey this year. :)
Working great! One question is there a way to Dim a column as Phone number?
David Zimmerman Hi I'm glad it's working well. I would Dim a phone number as text. Since you wouldn't be performing any numerical functions with it.
Very good explanation and useful video, big thanks.
Thanks so much. I am really glad you enjoyed it.
Excellent work ,
but
I think before watching your videos i need to learn atleast basics of vba,
Should i go with that free course of yours or any you tube video for that????
HI and thanks. Yes it is a good idea to have some VBA fundamentals first. I have a free VBA course taught by MVP Sumit Bansal on my site right here: www.excelforfreelancers.com/enroll-in-the-free-excel-vba-course-by-mvp-sumit-bansal-of-trumpexcel/
Really innovative method for filtering data! Thanks for sharing, Randy.
You are very welcome. Happy to share. Thanks for your comment.
Awesome tutorial
Thank you so very much, I really appreciate that.
Would this work if we have several worksheets ( tables) with different data, like Customers, Invoices, Invoice Items,......... Do we have to create an individual Filtering Modules for each sheet/table? How to differentiate/name them? Please advise.
Hi and thanks for your comment Moe, The best way to do this is to hide the original database for each sheet. Then create a new 'List' Sheet for each database. You can then filter & sort these sheets however you want. So you would have a separate sheet for Customer List, Invoice List, etc.
Thank you this is very useful, I have adjusted it to show a list of my movies. I have one problem, I want to protect the work sheet so the data can't be altered but when I protect it I get Error: Selection.AutoFilter . It works perfect until I protect the sheet. Been scratching my head for days..
Hi and thanks for your comment, When protecting your sheet you can select the 'Use Autofilter' option which will allow users to use that. Also through the vba code you can add the xlUserInterfaceOnly command which will allow VBA to make changes that users cannot.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Appreciate your work! I have a question: After searching the text, the macro is selecting the whole row. How can I select only 1 cell?
Thanks
Hi and thanks very much, you can select a single cell using something like this Sheet1.Range("A1").select
@@ExcelForFreelancers Thanks for your reply. I used this: Range("J7:J" & LastRow).Select and it worked. Cheers!
Thats great to hear. Very well done.
Thank You Very Much
You are welcome
Hello there! hoping you can create this kind of filter using ms office script :) this is very helpful!!! thank youu!
Hi and thanks so much. I will do my best for training in the future. Thank you for your Likes, Shares & Comments. It really helps.
Excellent tips regarding the flashing reaction.
I have one question in regards to the macro "Tablefilt" how come the "If" is not expecting an "end If"? Am I missing something?
I found the reason. When using the If in one line statement then no need for the end If. Everyday one learn something new. :)
Chuck, not only you, but I am learning every day as well. I think I have learned more in Excel this last year while teaching it than I did 5 years before. We are ALL students, so I am learning right along with you.
The idea is if you want just 1 thing to happen after an IF, then the End If is not required, however if you want multiple things to happen after an If then you will want to enclose them with an End If.
I hope this helps.
Great tutorial! But I have a doubt, whether we can apply this technique on a proper 'Table'?
Hi and thanks you can apply this on any table at all, or just use a range. both should work fine. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Thanks for your prompt response
@@ExcelForFreelancers I have created an advance filter macro for same kind of application and it wotks well, but the issue is any row with a blank cell (any of the criteria) is not getting filtered. Can you please help me sort it out?
HI Asif, can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 30,000 Members who would love to help you with this. Thanks so much.
Hi Randy, Thanks so much for all the examples you have online. I don't know any VBA, but have been able to put quite an elaborate file together for work. As usual, I've modified your example here. I have two criteria for my filter to be empty...If .Range("A8").Value = "J" or "A" Then RName = Empty Else: RName = .Range("A8").Value 'Rep Name
. My question is, how do I enter the J and A so it reads both? Thanks so much.
Hi sharing i am glad you like the videos. For using Or within the code you can write
If .Range("A8").value = "J" or .Range("A8").value = "A" Then RName = Empty Else: RName = .Range("A8").value
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
@@ExcelForFreelancers Thank you so much. I'll join the FB group too.
This is exactly what I need thank you very much for this!!
For sure, you are very welcome Dion. I am glad I could help.
Works better adding a change event in the fields, added like an activex control, so that the data is filtered as it is written and we don't have to press enter. Just a suggestion. Thanks.
Hi thanks that is a good suggestion as well, however Active X does not always work well with Mac's and with large tables adding so many controls may be a bit slow however on a smaller table it's a very nice idea.
Thanks for your suggestion.
So if i would want to filter without pressing enter, how would this be done?
Excel For Freelancers, Thank you for all the videos that help me very much.
@@kytan13 I can send you the VBA code. It is simple. Send me your email
Congratulation, a great video.
I am quite new at VBA and need your support. How can I search also for names with Greek characters?
HI Ute, thanks for your message. While I don't personally have experience with Greek characters, make sure you change the fonts in the VBA Editor and your computer as well (You may need to restart after this) I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
I enjoyed this video and ever since i watched i learnt.Thank you for sharing!
Thanks a lot Dev, I am really glad you liked it.
Thank you for the clarification.
For sure Chuck, no worries.
Thank you very much for this very helpful tutorial.
I want to know how to develop this more. Suppose I want to filter by "Name" column by entering more than one word(2 words or more), how can I do this
HI Jamal, thanks for the great question. Yes its possible using OR in advanced filters. First you would have to separate the two words in VBA. Once separated Using the same Criteria in different columns in the Advanced filter will provide the OR function. You can see my training on Advanced filters to see how the OR would work here: th-cam.com/video/YuCrEWU2Tm0/w-d-xo.html
@@ExcelForFreelancers what I want is to get the cells in the "Name" column that contains the two words (or more). So I guess we need AND not OR what do you think. If yes how it can be done?
Please watch the video in the link above as I explain both AND and OR functions. Thank you.
@@ExcelForFreelancers Than you
Realy exceptional. Thank you. Please update the download link.
Hi Thanks so much for your comment. I did just make an update to the Facebook Messenger link which should be working well for downloading the workbooks. (Email link for download should also be working)
Please let me know if you have any questions.
Hi I am very new to Excel VBA. I like your Table Header Filter and it works well for me. I, however, have a question. I have a column (ID No/Social Security Number). column. How can I modify the code so that I can also search on a field with numbers?
Thanx
Hi you would want to make sure that the formats and the variables are set to numbers so that it ensures excel uses #'s. Also make sure to remove the "*" from before and after the search filter variable. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Thanx very much for the fast reply. I posted on the group as you suggested.
Great Dawid, I hope we were able to help there.
sorry I was wondering how do you set them to numbers through this worksheet without messing up the codes
Excellent tutoria
Thank you very much. I do appreciate that.
awesome video. I just want to know that I want to use this filter in Excel in moible but mobile excel does not support VBA. So is there any other filter which i can use in mobile to search data regarding a customer such as mobile number, address etc.
Hi Excel is not very powerful in mobile so maybe you would want to use Google sheets when it comes to mobile (because of the lack of VBA in Excel in mobile)
I'm new into VBA and starting from scratch beginning with your first videos and I would like to thank you for these videos.
when I applied this tutorial step by step, the macro didn't show any error but the filter hided the whole results in the table and didn't show any result (when trying the tutorial excel file that i downloaded it also didn't show any result). I tried mm/dd/yyyy and dd/mm/yyyy formats with no success, any idea what might went wrong?!
Hi and thanks for your comment. When it comes to using dates in criteria its better to link to the source (and not have VBA add the criteria) The date should not be formatted as a date and should be in number format. So something like=C5 (so it would appear like 45100 for today's date. or something like +">="&C5 so it would appear >=45100
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi
Can you suggest me any video by which after filtering data, filter data can be extracted to other place and Depending on the filter information, everytime time the extract data will be changed.
Wishing for your suggestion
Hi Sajeda, sure this Advanced Filter video here explains that process: th-cam.com/video/kiQ5a_iJUcQ/w-d-xo.html
First of all, Thank you so much for the video..
In above video suppose we type 'John' and it filters out and shows the result,. In the filtered out result can we update/manipulate data such as changing contact info so that it gets updated. Is it possible to do so??
If yes please let us know.
Thanks.
😊
HI I am glad you like the video. In the workbook above, this sample you can update easily by changing the data at any time. You can then re-run the filter again if you like. Make sure you download the free workbook (link in the description) and try it out for yourself.
I tried, I works. Thank you so much.
For sure, you are very welcome
Hi Randy. Really enjoying the learning development and experiences gained from your tutorials and use of downloaded workbooks.
Unfortunately I have hit a snag with the use of "Selection.Autofilter". When I run the code, I am immediately returning the error, sub or function not defined, high lighting "Auotfilter" .
Here is a section of the relevant code which I hope will be enough for your to help identify where I might have gone wrong:
Selection.AutoFilter
With .Range("C5:X" & lastrow)
AutoFilter field = 4, Criteria1:=">=" & TDate, Operator:=xlAnd, Criteria2:="=" & BDate, Operator:=xlAnd, Criteria2:="=" & SPlan, Operator:=xlAnd, Criteria2:="=" & RDate, Operator:=xlAnd, Criteria2:="=" & NextAss, Operator:=xlAnd, Criteria2:="
Hello Stephen, Try to clear the filter before running a new filter, and also check your protection settings. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
I would like to learn VBA fundamentals is there any good book or tutorial for this
HI and thanks I also got your email with that question and responded:
I have some great courses on my partners page to learn the fundamentals here:
www.excelforfreelancers.com/partner-courses/
Thanks so much.
Hi Randi,
I was wondering if you could list in your web site the list of the Excel workbook you created in order to download easily as each time one wishes to download a workbook one has to go and re-enter the user id and password. This is just a suggestion.
Regards,
Chuck
I agree with you, Chuck, it's kind of a pain, so I am developing a single workbook library with my most popular applications all in one. This should be a really great and very fairly priced application so you can have a complete VBA Application Library at your fingertips.
Randy I need your help
can you let me know how to filter using numbers for this worksheet
I was reading some of the comments and I know they are old but some other people had the same issues if you can just help us out.
Hi Roland, you will want to make sure to format all columns as numbers, and make sure you do not add an asterisk before or after the numbers, so they are treated as numbers, not strings.
I hope this helps and thanks so much.
Hello,
Firstly, thank you for producing these videos. I am learning a lot and it is supplementing my further readings on VBA/Excel. This is a personal hobby and not my day job.
My question: I love the filter in the header and learned quickly that the data can't actually be in a Table w/header (unless the data is in a table without the headers and you created the headers separately?)
Anyways, I have a column of data that is conditionally formatted. The header is "Complete" with a simple Y or N to trigger the CF. IF yes, nothing happens; however, if N then the square fills with a light red. Very basic. I have created a filter for this column and it works for Y (unformatted) but not for the N (which has the fill/format). I would like the filter to also work for the "N". Do you have any suggestions without removing my Conditional Formatting?
Thank you again for your awesome Videos!
Dave
Hi that is great to hear, thanks so much for your feedback. I am glad you are enjoying these videos.
Excel For Freelancers what do you think about my problem? Any solutions come to mind quickly? Also, I don’t have Facebook.
Hi Dave, sorry i missed you original question. I am not sure I understand the issue entirely. If you have entered both Y and N as text in the table, the filter should work properly and you should also be able to assign regular or conditional formatting to either of these without issue. Perhaps you can upload the application or screenshots in our Excel For Freelancers Facebook Group so we can take a closer look at it. I know we will find a solution for you: bit.ly/groupexcel
Is it possible to filter data by one column topic OR the other? I mean, there are times I don't know whether a piece of data appear in column F or G, so can I set the code to look for it in either one but not BOTH altogether? So the table returns me either rows with said data in column F, but also rows with said data in column G?
Hi you can use Advanced filters in VBA to check for both results, one at a time and then post all of the results. I will try to make a video on this within the next few months, however please feel free to ask this question in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel we have 23,000 developers who would love to help you with this.
Hi Thanks for sharing knowledge. I have tried your VBA for my data. While everything is working fine, date filters are not working. I am working on an dd/mm/yy format in my excel file. Does it make any difference? Can you please help?
Hi and thanks very much. When it comes to date filters, use a 'General' format in the criteria and not any other formats. This should filter the date properly. You an also use a formula in your criteria directly to the source of the date. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers Thanks a lot for the prompt response.
very useful!
Thanks so much Steven
Hello sir than you for this, I have tried it and it works.. only problem is when i tried typing a name not included in the selection and did it twice, the other rows automatically hides and the filter moves up unless i again clear the selection, it then goes back to normal not hiding the other rows. pls help
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 50,000 Members who would love to help you with this. Thanks so much.
Great vídeo! Thanks for sharing!!
For sure, you are very welcome. I just uploaded a new one on filters thats different: th-cam.com/video/kiQ5a_iJUcQ/w-d-xo.html
Very helpful, thank you. In some cases of I don't clear the filter before I change the values, it applies the filter to the header and it flips the input field with the header. Any idea why?
Hi Francesco, It is important to clear both the Criteria and Results before adding new criteria then running the advanced filter. Also you should often Delete the criteria before a new advanced filter (If you run an advanced filter with no criteria, in other words you just want unique lists. Excel 'remembers' the old criteria, so its important to delete the named range "Criteria" before applying a new Advanced filter. This can be done via VBA. You can find some examples of how i Delete this in this video here: th-cam.com/video/kiQ5a_iJUcQ/w-d-xo.html
Hi
Thank you very much for the tutorial. This helps me a lot to solve a problem fast. But I have one question.#Question: After doing all the steps, I can easily filter according to my need. But it is only for once. I mean this micro is not saved. When I open my file again, and try to filter, i Cannot do it. I have to write the code again and they it works. So any way to save it to my file. So that it becomes completely auto.
Hi thanks very much. You can save your filters in another table in order to recall them. It's best to separate your actual data in one sheet and have the filtered data on another sheet. This way when you re-open your workbook, nothing changes.
Here is another video with some Advanced filtering: th-cam.com/video/kiQ5a_iJUcQ/w-d-xo.html
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Hi
FOR TEXT TYPR, you use them as string
For example, Dim Contname As String
So, what i will use if I have number to search, like no text, it a postcode such as 1200, 12300, etc. So how i will address them?
sajeda mony whole numbers can be Dim as Long. Just don't use the "*" at the beginning and end of the variable when filtering. I hope this helps should you have any additional questions please join our Excel for Freelancers Group where we have over 8,000 Excel professionals who can answer your questions. bit.ly/groupexcel
THANKS
For sure, you are very welcome
This is great! But how can I make it more dynamic by not having to press enter and getting data to show up just as I type?
Hi you would want to use an ActiveX component instead of a cell-based component if you want to filter results based on what you type. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Same query here
I liked so much your tutorials
Thank's so much. I appreciate that.
Thanks, its very helpful.
I do have a problem.
I changed the name column into numbers and the filter wouldnt work, can you please help me in making the autofilter to work with numbers?
Thanks,
Hi thanks for your comment and I am glad you find it helpful. You will want to make sure your criteria is using the same header name as your original data within the advanced filters. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Actually, I figured it out.
for criteria1, instead of using wildcard, which is for text, I changed it to equal to the name of the column
That is great to hear. I am glad you have figured it out.
Nice video
Thank you so very much, I really appreciate that Islam
Thank you sir so much for this tutorial
I have a question about the format of date because i want to lunch search with this format ("dd-mm-yyy") what i need to do to change the date format in your code
Sorry my english is not so good ^^
Hi when it comes to date formats you want to make sure to check 3 things:
1) Your computer has the right date formating in Windows or Mac
2) The cell has the same date formatting
3) In the VBA Code you can format any date using this code "Format(FrDate,"dd-mm-yy")"
I hope that helps. Please feel free to join our group here for quick and fast help: bit.ly/groupexcel
Thank you sir you are the best ^^
It's possible to Create Dynamic Excel Filter - Extract data as you type
Hi it may be able to be done with an activex or form control field as those types of boxes have events on change, while cells have macros that run only when a user exits a cell. I believe their are some videos on TH-cam describing this so a quick search may be helpful. Thanks very much
Hi. I know I'm late to the party, i just want to check, in the last section of the video. Do you still hit enter to bring up the search results? I know it's meant to be automatic but even with the coding mine does not bring up the results by itself. My data is grouped. Would this influence this function?
Hi and thanks make sure the change event is being activated and also feel free to step through the code using F8 to check for exits to the code. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
In Excel, How to filter data by using created searchable drop-down, and filtered name to be reflected on header. Or any other options?
Hi Bharat, you can add a data validation in your filter along with a auto-complete to help users. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Sir, is this macro run in locked CELL or SHEET also.
Hi Sandeep, thanks for your comment. You can try protection in VBA with the setting xlUserInterfaceOnly, otherwise you can unprotected it and reprotect it within the code. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Nice video 😊😊
Thank you so very much, I really appreciate that Saiful
Instead of putting the Clear Filter with True/False in a cell on the spreadsheet, could you have also just defined a variable above the first Sub in the module?
HI Scott. thanks for your comment. We want to make sure we differentiate between two types of worksheet changes. One in which the user is making a change when they want to filter, and the other change by VBA when clearing filters. Setting a True/Values value in a cell helps us differentiate between these to changes when triggering Worksheet_Change events. There may be several ways to accomplish this. Thanks for the great questions.
Hi,
Thank you for this great video. Just want to ask for the number data. If the input value is not on the range, how to filter nearest higher or nearest lower number? Is this possible? Thank you.
Hi use can use Greater than > or Less then < to filter out numerical data on advanced filters. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hi! I did you a consult about a search/filter box in another video and here I found just what I wanted!!! But I applied it to my Contact Manager spreadsheet, and now some of the original functions don't work! The info of the contacts is not loaded into the respective fields when I click on one, and the color bar doesn't shows up either. Help me! What did I screw up?
Hi Gabriel, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
Hi,
Could you please do a video on how to create a "SAVE DATA" button. Would like two things to happen when the button is pressed: 1 the data is save in the same workbook (in let say sheet 2) under the respective heading.and you get a message saying"DATA SAVED" and all all cells that have entire in it are cleared. Some cells having drop down. Would be a great help on this work book I am currently working on.
Can send it to you and discuss more on it.
Thanks, Regards, Marcus
Just one more thing, I want it to save the data no matter how many times the same data is typed. Example if I have a customer who comes In X number of times want X number of times the data saved.
Hi Marcus, thanks very much. I have been covering a lot of that in our Employee Manager series which I think you will find really helpful. There are tons of features here which will help you a lot to learn on Saving Data and all of the options that go with it. Its a multi-part series, so far 3 parts, but a new part comes out every Tuesday. This is what I have so far:
Part 1: th-cam.com/video/iyr9WIyxlJc/w-d-xo.html
Part 2: th-cam.com/video/lba5z5RlzxI/w-d-xo.html
Part 3: th-cam.com/video/5UTLbi4W9aw/w-d-xo.html
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Love the program.
I have 2 search fields A5 and C5, ContName (dim as String) assigned to A5 and Facility (dim as string) assigned to C5. I get no errors, however only field A5 (Field 1, Cell A5 ) searches correctly. C5 (Field 3 Cell C5) continually searches correctly but displays no data. Is the following code correct?
If ContName Empty Then .AutoFilter Field:=1, Criteria1:="=*" & ContName & "*"
If Facility Empty Then .AutoFilter Field:=3, Criteria1:="=*" & Facility & "*"
Hi Rhonda, Thanks for your comment. I would use F8 to step through the code line by line. To make sure the values are correct. Also make sure the values are also formatted as text in your table. If it still does not work, we can probably fix this for you.
Please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Awesome, as always.
Thank you very much :)
Hi Sir, how to filter if the field is double (e.g. "1.001", "3.004") using autofilter. I would like to ask the script for that. Thank you
HI and thanks this tutorial teaches you how to do that via Advanced filter in which you can filter in place or another location. you will want to specify filters based on a number or text and then make sure the fields are formatted properly.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
I am new to VBA, one thing I'm curious is how does the Dim statement recognize the Name field as ContName and Address as ContAdd. Like can you just simply key in a name in the module form by keying in Dim followed by whatever name you want and "As String" or "As Date". I'm not sure how does it all works. Also, I'm creating a similar spreadsheet but I have more fields (some are numbers and some are texts). Do I need to make more Dim statement s and write in the module = "Dim (whatever name I want to refer to another field I want). As String"?
Hi it depends how you want the variable used. For example if you want to use a variable in math or find the maximum you would want to dim it as long or double (which are numbers) and if you want to format a variable as a date then you would want to Dim it as a date. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Hello, i have problem, it cant search when i type name in Enter Name field. i run program but only appear pop up (thing) and i can search there when i click on pop box and tipe in search box, but it cant search in field above. Any solutions ? Thanx, you help me a lot
Hi Nidzo. We would have to see screenshots of your code and workbook to understand the issue. Can you join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel and please post your detailed question along with all of the screenshots. We have 25,000 members in the group, ready to help you.
Hello Sir please provide this Workbook/VBA Code
Hi, you can download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
Thank you sir❤