I don't know what I'd do without you Paul. You seem to have the best solution for everything I ever look for. I'll probably search your channel when I want to know the next winning lottery tickets. This videos are so valuable. Thank you!
I searched high and low to find advice on how to quickly copy data between two tables ... and found nothing. Here's the answer and brilliantly illustrated. Thank you Paul. I've been coding in VBA on Access for years and thought Excel was just rubbish at handling large volumes of data.
Fantastic tip....I have been struggling a little to speed up a long macro. I used this principle to shave off about 5 minutes of vba run time. Thank you for your time and effort in making these videos.
I had a project years ago that did filtering on over 23,000 items and then sent out an email to various users with different error codes. At the time, it checked against 3 conditions and still took over 40 minutes to process. I rewrote the code using advanced filters and dropped the total tune to under 5 minutes. They didn't know how many conditions would be checked when they were finished so I added a feature to allow them to define a many as they wanted without requiring another rework of the code. At last count they were up to 38 conditions being checked with no significant overhead in time requirements. I've been a fan of advanced filters ever since. Great info here.
whether you are pro or intermediate VBA user, you will always find new and efficient ways of writing code. Thanks for your content and wonderful presentation skills.
You are a good teacher. I like how you explain things step by step instead of the whole thing at once. Your way allows me to find my error when I do something wrong.
Delighted to see someone else who has seen the light with the Advanced Filter. I usually joke with people that since most people don't know about it, it must be a new feature. That it was in the first version of Lotus 1-2-3 perhaps explains some of the arcane rules for setting things up. One super easy application is not just to copy data, but to rearrange the order of the columns (a task that people often struggle with). Using the extract range with the new order of all or a subset of the columns is the fastest way to copy AND rearrange.
I didn't realise it was around so long! Thanks for the info. Selecting columns or rearranging them is one of it's biggest strengths. Because using other methods requires a lot of extra code to do this.
Thank you for sharing this and explaining in a straight forward way. I am no VBA master but using this has allowed me to help someone with a document that took them ~8 hrs to produce manually and now takes ~10 seconds (VBA benchmarked)
This advance filter reminds me of Lotus 1-2-3 from back in the '80s. I remember having to create the criteria and output range to filter data in Lotus 1-2-3 and it filtered really fast. But Excel's ALT D F F short cut combination for filters, not having to create the criteria or output range and pivot tables were a lot more intuitive. Thank you for sharing your knowledge!
it's very helpful, i've been looking for this kind of advance filter a couple of weeks ago. thank you sir, it might be a great help for my system working on
Brilliant! I love the appropriate level of detail. It makes your videos so useful. FYI, for anyone that needs it. A relative date can be used in your criteria like this: =">" & TEXT(TODAY()-10,"mm/dd/yyyy")
Really love that you also explain other function that doesn't directly relate to advancedfilter. It help very much for beginner like me to understand the video. Looking forward if you have any video to explain on event function. lol
I feel the urgent need to thank you once again so much for this truly nice tutorial. I followed all the steps on my PC and I am very astonished how great this code is. Really thank you Paul.. You are excellent. Thank you.. 🌟
Ditto, came here to say the same thing... I’m new to VBA on UA-cam having worked my way through a physical book first. I found your information was distinct, the descriptions clear and presentation was both well paced and good vocal tone. This was perfect to follow and learn from. Thank you very much.
This is super fantastic. I always used ADO but this is a ton simpler. There were also formatting problems with ADO that this method doesn't have. Thanks so much!
Hello, thank you for this helpful video. But I have a question based on your example. What if I Need the data for those who has quantity more than 2 but less than 8? How can I set the criteria?
This is mind blowing and exactly what I've been looking for, for ages now! Thanks so much! I plan to integrate this code with a loop that goes through all the individual values of the criteria and repeats this process for each filtered list. And then it outputs the results into a sheet named by an string in a cell offset from the criteria and exports that sheet into a new book (also named by that value). I have code similar to this except it's all manual and unreadable, but I've been looking for a way to do this as a filter method for months! THANKS SO MUCH! Earned that sub.
@@ignatiusmusonda6658 unfortunately no, i havent really touched it since i couldnt find the time and i do these things rarely (but am passionate about them nonetheless). You might be the motivation i need to finish it, might try in spare time since i no longer have free time at work for this. Will let you know!
@@ignatiusmusonda6658 I finished it :) I think it would be messy to post it here, find me on FB or TG and I'll send you the code. It has a few magic numbers/things you need to tweak for your own purposes, but other than that it works, just tested it in the office a few hours ago.
Wonderful Tip! Well explained, as always @Excel Macro Mastery. I would also recommend a spirit of inquisitive exploration when practicing these methods. I found the next video and its discussions about how to implement AND and OR operators with the advanced filter to be especially useful; I used both explanations to streamline a worksheet that needs to filter the data range based on days of the month. A little more explanation of how the advanced filter implements criteria would have been useful, but it wasn't anything that some playful experimentation couldn't solve.
Very clear and concise tutorial video. I can see that your channel is rocking and rolling already and long may it continue! Thanks for putting the content out... You're fast becoming an Irish Randy Austin on UA-cam (and I believe that's a huge compliment).
Nice. I've used Advanced Filter before, but was never clear on the use of Criteria Range. I'm hoping to use this VBA feature to be able to filter from one workbook and copy the data to another. The source worksheet has 61 columns all the way to BI, and variable rows. I only need data from 7 of those columns. And to make it interesting, one column has random blank cells! Should be fun! Thanks Paul!
Hi, I am completely new to VBA so not sure if i am throwing my self in at the deep end. Great video really helped me with building a workbook which makes it very easy users to quickly review information. However, i wanted to take this one step further... I have two sets of data on the same sheet (e.g B7:E32 and B36:E61, with the criteria range B1:B5 to allow me to filter by multiple criteria in same col) the data ranges will use the same criteria when filtering. I tried to tweak the code slightly to filter both data ranges at the same time but it seems to 'hide' the rows on the first set of filtered data (B7:B32 range) Sub AdvancedFilterInPlace3() Dim rgData As Range, rgData2 As Range, rgCriteria As Range Set rgData = ThisWorkbook.Worksheets("Sheet3").Range("B7:E32") Set rgData2 = ThisWorkbook.Worksheets("Sheet3").Range("B36:E61") Set rgCriteria = ThisWorkbook.Worksheets("Sheet3").Range("B1").CurrentRegion rgData.AdvancedFilter xlFilterInPlace, rgCriteria rgData2.AdvancedFilter xlFilterInPlace, rgCriteria End Sub any help would be greatly appreciated Thanks
Thank you so much for this step by step tutorial. It was very easy to follow through and apply it to my own data. I have never used advanced filter before in my life but now I'm a believer. Subscribed!
I find all the explanations extremely clear and understandable. Thank you! SUPER BRAVO! One problem: when I use the function to copy to another sheet: rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput I get run-time error 1004 how can i avoid it? Thanks again
Thank you, the presentation was excellent and easy to understand. When ran the code it worked beautifully, however the filtering also remove duplicate records.
Hi Paul.. this video is really excellent. I've made up my own data set to follow along and reinforce the concepts and code. Thanks for this super tip and technique. Really enjoying your UA-cam channel.. I think it is one of the best resources out there for learning and expanding VBA knowledge. Thanks for kindly sharing your skills.. much appreciated. Thumbs up!!
Thankyou paul for your video.. I think we can change the "rgcriteria" to be = range("a2").currentregion so we aren't to make calculation " if range("a1").currentregion.row.count = 1 " everytime it doesn't intersect
Thank you very much for this helpfull video. One question, I have to filter a tons of data using differente filters; for example: Col1 = Filter1, Filter2 & Col2 = Filter1, Filter2, Filter3 and so on. What should be the best approach to filter all the data using diferente filters with more than 1 value?
I've been using advanced filter in combination with a textbox. When you type it automatically filters the string. Useful when you have a huge list of files and folders.
Very good video! I learned a lot from it and the explanation is very clear and easy to follow. Amazing channel as well. Thank you for taking the time to show us.
Hello Great instruction it help me a lot with data extraction is there any simple way of extracting data from multiple worksheets in the same workbook ?
Thanks a lot. I used to depend on pivot tables but now I will use Advanced Filters instead which is much cleaner and faster. I can use it to filter data based on a user criteria, create a subset by workbooks.add and mail it to user.
Hi....thanks for the video...it's really helpful. Could you share a video where we need to look up the highest value for data in each row for a range of 10000+ rows and then bring the value in the next column for each row in a separate row
I really like your videos. They are inspiring but they always lead me to challenging myself to implement what you are sharing. I am trying to use Advanced filter to select ranges with the same number of columns however the number of rows varies and copy them to another worksheet. In my specific case I have 8 filtered ranges that i am enter into a table on another worksheet. Any advice?
Hi Paul. As always useful stuff and usually a nugget that I didn't know before. I wasn't aware of the Intersect property and always manually tested the results of the Target cell. I was surprised to see you could output it to a second tab. Since you can't copy to another sheet from the Advanced Filter dialog box, I never considered it would be available through vba. (although with Microsoft that doesn't really surprise me)
Reading your comment made me realize I could not copy the results of the Advanced filter on the same sheet. And I paid more attention to the output part of the VBA code
Hi Paul Kelly, Thank you for the content. I discover your channel recently and i'm binging it. I really like the AdvancedFilter function but I was wondering if i could use it but without criteria? The reason for that (if it makes sense) is that I want to copy the entire range from my Export table and copy it in my Analysis sheet where some columns have been added (for calculation) and some columns have been removed. In this video it seems that in your Output sheet, you can customize your header to match the copy/paste range. I hope i'm clear enough. Thank you !
Hi, Love your videos. I have a question. For your example on your criteria range on rows 2, what if I had more rows, like for Sales Person you want to find out for "Tom", "Mary" and "John" with sales over XX amount.
Hi, loved the video. Just to note on the Mac this produced the following error: [Run-time error '9' Subscript out of range] Sub UseAdvanceFilterInPlace()
Dim rgData As Range, rgCriteria As Range
Set rgData = ThisWorkbook.Worksheets("Data").Range("A10").CurrentRegion
Set rgCriteria = ThisWorkbook.Worksheets("Data").Range("A1").CurrentRegion
rgData.AdvancedFilter xlFilterInPlace, rgCriteria End Sub
Thanks for the video. It works perfectly as shown, except when I am trying to paste the filtered results to an existing table. When copying to an existing table it only copies the header rows. Is there anything we have to do differently when copying to another table ?
Hi, thank you so much for this. This is extremely useful, I was wondering how you would write the code in order to exclude data. For example, say that you want to exclude all data that includes Ann? Thanks
Thank you for the tutorial - great insights - Q: how can I add a wildcard (*) before the name - as you know text fields may not have one name or one word, and the word one search for in many cases is located in the middle of the text - Please let me know of the add code to do this, Thanks!
Hi, thanks for posting this! It has really helped with a problem I have been facing :-) I have one question. Would it be possible to extend this macro so that it looped through a number of different filter criteria? For example, could you populate row 2 and 3 with different criteria and the macro would take both sets of information and paste them in the output?
Thanks for the tutorial. What if you're using one condition in another worksheet, and the output should be a csv saved in a specific folder without the header? How do you check the time spent in running the macro?
FLASH SALE: 25% off my Effective Excel VBA course before Saturday 14th September: 👉courses.excelmacromastery.com/courses/Effective_Excel_VBA
Quick guide to filter criteria syntax here👉: excelmacromastery.com/vba-advanced-filter/#VBA_Advanced_Filter_Quick_Guide
Thank you.
I have a question. Can you copy the filtered data to another workbook?
how about clipboard ?
Hi, on 18:35 the ClearFilter is not working for me. how to solve this issue? Thank you.
How to put new data in the next empty row in Output sheet using advanced filter?
can you advance filter by format, example highlighted cells?
I don't know what I'd do without you Paul. You seem to have the best solution for everything I ever look for. I'll probably search your channel when I want to know the next winning lottery tickets. This videos are so valuable. Thank you!
I searched high and low to find advice on how to quickly copy data between two tables ... and found nothing. Here's the answer and brilliantly illustrated. Thank you Paul. I've been coding in VBA on Access for years and thought Excel was just rubbish at handling large volumes of data.
Glad you found it useful Simon.
Fantastic tip....I have been struggling a little to speed up a long macro. I used this principle to shave off about 5 minutes of vba run time. Thank you for your time and effort in making these videos.
That's great to hear Donald. Advanced Filter is a well-kept secret which I'm hoping to change.
Just what I was looking for! 👌👏
I had a project years ago that did filtering on over 23,000 items and then sent out an email to various users with different error codes. At the time, it checked against 3 conditions and still took over 40 minutes to process. I rewrote the code using advanced filters and dropped the total tune to under 5 minutes. They didn't know how many conditions would be checked when they were finished so I added a feature to allow them to define a many as they wanted without requiring another rework of the code. At last count they were up to 38 conditions being checked with no significant overhead in time requirements. I've been a fan of advanced filters ever since. Great info here.
Thanks for feedback. It's great hearing about a successful real world project.
whether you are pro or intermediate VBA user, you will always find new and efficient ways of writing code. Thanks for your content and wonderful presentation skills.
You are welcome!
You are a good teacher. I like how you explain things step by step instead of the whole thing at once. Your way allows me to find my error when I do something wrong.
Thanks Linda.
Delighted to see someone else who has seen the light with the Advanced Filter. I usually joke with people that since most people don't know about it, it must be a new feature. That it was in the first version of Lotus 1-2-3 perhaps explains some of the arcane rules for setting things up. One super easy application is not just to copy data, but to rearrange the order of the columns (a task that people often struggle with). Using the extract range with the new order of all or a subset of the columns is the fastest way to copy AND rearrange.
I didn't realise it was around so long! Thanks for the info.
Selecting columns or rearranging them is one of it's biggest strengths. Because using other methods requires a lot of extra code to do this.
Thank you for sharing this and explaining in a straight forward way. I am no VBA master but using this has allowed me to help someone with a document that took them ~8 hrs to produce manually and now takes ~10 seconds (VBA benchmarked)
That's awesome to hear.
This advance filter reminds me of Lotus 1-2-3 from back in the '80s. I remember having to create the criteria and output range to filter data in Lotus 1-2-3 and it filtered really fast. But Excel's ALT D F F short cut combination for filters, not having to create the criteria or output range and pivot tables were a lot more intuitive. Thank you for sharing your knowledge!
You're simply great sir😍
it's very helpful, i've been looking for this kind of advance filter a couple of weeks ago. thank you sir, it might be a great help for my system working on
Brilliant! I love the appropriate level of detail. It makes your videos so useful.
FYI, for anyone that needs it. A relative date can be used in your criteria like this: =">" & TEXT(TODAY()-10,"mm/dd/yyyy")
Thanks Greg. That's a very useful tip.
This is the best advanced filter tutorial I have seen in years. Excellent Presentation.
Thanks Daniel. It's great to hear such a positive response.
This makes coding so simple, easy to read and makes the size of the files smaller. awesome. keep up the good work.
Really love that you also explain other function that doesn't directly relate to advancedfilter. It help very much for beginner like me to understand the video. Looking forward if you have any video to explain on event function. lol
This video has some very useful information that might help me improve an existing macro. Thank you!
There is always at least one thing I didn't know in each of your videos! Thanks again Paul, for sharing your knowledge
That's great to hear. I always like to come up with little-known but very effective techniques.
I feel the urgent need to thank you once again so much for this truly nice tutorial. I followed all the steps on my PC and I am very astonished how great this code is. Really thank you Paul.. You are excellent. Thank you.. 🌟
Thanks Salim, That is great praise to hear.
Ditto, came here to say the same thing... I’m new to VBA on UA-cam having worked my way through a physical book first. I found your information was distinct, the descriptions clear and presentation was both well paced and good vocal tone. This was perfect to follow and learn from. Thank you very much.
Very helpful information . It help me solve a current challenge Thank you😃
Excellent. I have to learn from basic. Please guide me. Your way of explaining is really superb. Hats off to your god's gift talent.
This is super fantastic. I always used ADO but this is a ton simpler. There were also formatting problems with ADO that this method doesn't have. Thanks so much!
Hello, thank you for this helpful video. But I have a question based on your example. What if I Need the data for those who has quantity more than 2 but less than 8? How can I set the criteria?
You are my goto guy for anything excel. I admire your consistent quality content 🙏
Thanks Prabhat. I appreciate the compliment.
It was nice and awesome video. Thank you for sharing.
This is mind blowing and exactly what I've been looking for, for ages now! Thanks so much! I plan to integrate this code with a loop that goes through all the individual values of the criteria and repeats this process for each filtered list. And then it outputs the results into a sheet named by an string in a cell offset from the criteria and exports that sheet into a new book (also named by that value). I have code similar to this except it's all manual and unreadable, but I've been looking for a way to do this as a filter method for months! THANKS SO MUCH! Earned that sub.
@
Alan Frančišković this sounds like what i need; have you made progress on this?
@@ignatiusmusonda6658 unfortunately no, i havent really touched it since i couldnt find the time and i do these things rarely (but am passionate about them nonetheless). You might be the motivation i need to finish it, might try in spare time since i no longer have free time at work for this. Will let you know!
@@ignatiusmusonda6658 I finished it :) I think it would be messy to post it here, find me on FB or TG and I'll send you the code. It has a few magic numbers/things you need to tweak for your own purposes, but other than that it works, just tested it in the office a few hours ago.
@@Lykoskia Great i just sent you a request on fb.
Wonderful Tip! Well explained, as always @Excel Macro Mastery.
I would also recommend a spirit of inquisitive exploration when practicing these methods. I found the next video and its discussions about how to implement AND and OR operators with the advanced filter to be especially useful; I used both explanations to streamline a worksheet that needs to filter the data range based on days of the month. A little more explanation of how the advanced filter implements criteria would have been useful, but it wasn't anything that some playful experimentation couldn't solve.
Very clear and concise tutorial video.
I can see that your channel is rocking and rolling already and long may it continue!
Thanks for putting the content out...
You're fast becoming an Irish Randy Austin on UA-cam (and I believe that's a huge compliment).
Thanks very much Ben. I know of Randy Austin - he has built a very big community.
Nice. I've used Advanced Filter before, but was never clear on the use of Criteria Range. I'm hoping to use this VBA feature to be able to filter from one workbook and copy the data to another. The source worksheet has 61 columns all the way to BI, and variable rows. I only need data from 7 of those columns. And to make it interesting, one column has random blank cells! Should be fun! Thanks Paul!
You're welcome Russell. Sounds like an interesting project.
Hi, I am completely new to VBA so not sure if i am throwing my self in at the deep end. Great video really helped me with building a workbook which makes it very easy users to quickly review information. However, i wanted to take this one step further... I have two sets of data on the same sheet (e.g B7:E32 and B36:E61, with the criteria range B1:B5 to allow me to filter by multiple criteria in same col) the data ranges will use the same criteria when filtering. I tried to tweak the code slightly to filter both data ranges at the same time but it seems to 'hide' the rows on the first set of filtered data (B7:B32 range)
Sub AdvancedFilterInPlace3()
Dim rgData As Range, rgData2 As Range, rgCriteria As Range
Set rgData = ThisWorkbook.Worksheets("Sheet3").Range("B7:E32")
Set rgData2 = ThisWorkbook.Worksheets("Sheet3").Range("B36:E61")
Set rgCriteria = ThisWorkbook.Worksheets("Sheet3").Range("B1").CurrentRegion
rgData.AdvancedFilter xlFilterInPlace, rgCriteria
rgData2.AdvancedFilter xlFilterInPlace, rgCriteria
End Sub
any help would be greatly appreciated
Thanks
Thank you Paul, this is brilliant! you have an extraordinary way to explain things! I am buying your course on Udemy. I wish I met you years ago!
Thank you for your clean explanation of the subjects.
Okay MINDBLOWN!!!! I've been searching for a way to do this all day and this has literally smashed it! Definitely subbing to this channel! thank you!
Hi Paul! Just watching a half of this video but it is really awesome one. Never knew that advanced filter was so useful. 👍🏾
Thanks. Enjoy watching till the last minutes.
Can't thank you enough for these videos. Wish I discovered your channel earlier!
Great stuff ! Never new you can reduce number of returned columns to required ones although been using Excel/VBA for years.
excelentt video I have used a lot advanced filters in my reports but you gave me a new way of do it...
thanks!!!
Thank you so much for this step by step tutorial. It was very easy to follow through and apply it to my own data. I have never used advanced filter before in my life but now I'm a believer. Subscribed!
I find all the explanations extremely clear and understandable. Thank you! SUPER BRAVO!
One problem: when I use the function to copy to another sheet:
rgData.AdvancedFilter xlFilterCopy, rgCriteria, rgOutput
I get run-time error 1004
how can i avoid it?
Thanks again
Another one that puts me out of my misery with expert users! I just forward the link to this video!
Edit: Excellent content I wanted to add!
Thanks archibald
All your videos are simply awesome! I wish I had seen them before, I would have saved hours of head scratching code! Thanks
Thanks Denis! That is high praise indeed.
Very Helpful... Thank You... We need Excel masters like you...
Happy to help
Thank you, the presentation was excellent and easy to understand. When ran the code it worked beautifully, however the filtering also remove duplicate records.
Hi Paul.. this video is really excellent. I've made up my own data set to follow along and reinforce the concepts and code. Thanks for this super tip and technique. Really enjoying your UA-cam channel.. I think it is one of the best resources out there for learning and expanding VBA knowledge. Thanks for kindly sharing your skills.. much appreciated. Thumbs up!!
Thanks very much Wayne. Plenty more videos on the way.
Thankyou paul for your video..
I think we can change the "rgcriteria" to be = range("a2").currentregion so we aren't to make calculation " if range("a1").currentregion.row.count = 1 " everytime it doesn't intersect
This is very useful, Paul. Hope to see you more often.
You will Sandeep. Plenty more videos on the way.
wow sir your teaching way is very good 👍👍👍👍
Thank you very much for this helpfull video. One question, I have to filter a tons of data using differente filters; for example: Col1 = Filter1, Filter2 & Col2 = Filter1, Filter2, Filter3 and so on. What should be the best approach to filter all the data using diferente filters with more than 1 value?
Thank you for sharing this.
I wonder how do we use multiple criteria for a single column
I've been using advanced filter in combination with a textbox. When you type it automatically filters the string. Useful when you have a huge list of files and folders.
That is very useful applicaiton of it Jelle.
Thank you so much sir for your unwavering support and keeping us abreast in VBA programming.
Full support to your channel.
Excellent presentation, for ur explanation take a bow 🙇♂️
Thanks a lot Rohith
You are the men!
Thanks from Brazil!
Thanks Marcos
Excellent and Helpful Presentation. Appreciate your help.
Excellent video 👍
Thank you so much you r real hero for us
Love from India ❤️
This video is outstanding! It was so helpful.
Great Video ! It was a lot of help for the project I was doing.. Thanks a million !
Great to hear!
Thank you for sharing this course.
Very good video! I learned a lot from it and the explanation is very clear and easy to follow. Amazing channel as well. Thank you for taking the time to show us.
Hi Paul, your videos are great!
Thanks Marcel.
You're welcome.
Excellent stuff. Need more of these kind of tutorial.
Thanks paul for sharing the video.. But,, Why if target.cell.count >1 we exit sub??
Hope you have time to answer it. Thankyou
Thank you very much for sharing. great tutorial!!!
Damn... I'm amazed did it at work for filtering big files and it's getting my work done 10x faster
Big thanks!
Subbed and liked keep up the content :)
You're welcome.
I admire your videos Paul. You are amazing. 🌟 🌟 🌟 Thanks indeed for sharing. Excellent... Great.. 🌟
You're welcome Salim.
Hello Great instruction it help me a lot with data extraction is there any simple way of extracting data from multiple worksheets in the same workbook ?
Very nice tutorial. How do you make it also copy the formulas in a cell i.e transfer formulas to another range of cells
Thanks a lot Paul. This video (like all your videos) is really excellent
Thanks a lot.
Thanks for sharing your knowledge, really advanced
very useful
So glad that i met your video
Great video, Paul and instructive.
Thanks for the feedback John.
Excellent sir
Hats off to ur knowledge
Thanks a lot. I used to depend on pivot tables but now I will use Advanced Filters instead which is much cleaner and faster. I can use it to filter data based on a user criteria, create a subset by workbooks.add and mail it to user.
You're welcome John.
Excellence!!! Thank you for sharing this!
awesome tutorial you got here, thank you
Hi....thanks for the video...it's really helpful. Could you share a video where we need to look up the highest value for data in each row for a range of 10000+ rows and then bring the value in the next column for each row in a separate row
Can you clarify what you mean? From what you wrote it seems like a simple "=MAX()" formula copied down the entire column would work.
I really like your videos. They are inspiring but they always lead me to challenging myself to implement what you are sharing. I am trying to use Advanced filter to select ranges with the same number of columns however the number of rows varies and copy them to another worksheet. In my specific case I have 8 filtered ranges that i am enter into a table on another worksheet. Any advice?
Amazing THANK YOU Marco!!!!
Trust me you made my day by this video
Hi Paul. As always useful stuff and usually a nugget that I didn't know before. I wasn't aware of the Intersect property and always manually tested the results of the Target cell.
I was surprised to see you could output it to a second tab. Since you can't copy to another sheet from the Advanced Filter dialog box, I never considered it would be available through vba. (although with Microsoft that doesn't really surprise me)
Reading your comment made me realize I could not copy the results of the Advanced filter on the same sheet. And I paid more attention to the output part of the VBA code
Hi Paul Kelly, Thank you for the content. I discover your channel recently and i'm binging it. I really like the AdvancedFilter function but I was wondering if i could use it but without criteria?
The reason for that (if it makes sense) is that I want to copy the entire range from my Export table and copy it in my Analysis sheet where some columns have been added (for calculation) and some columns have been removed.
In this video it seems that in your Output sheet, you can customize your header to match the copy/paste range.
I hope i'm clear enough. Thank you !
Hi,
Love your videos. I have a question.
For your example on your criteria range on rows 2, what if I had more rows, like for Sales Person you want to find out for "Tom", "Mary" and "John" with sales over XX amount.
Great Tutorial! Thank you very much.
One question to you! Is it possible to paste below? If yes, How?
Very smart and fast way of extracting data. Please could you make the code available for practice. Thank you
Thank you so much for this video. My application is so much faster. Thanks for sharing your knowledge.
Great job Paul. Love this stuff.
Good to hear. Plenty more on the way.
Hi, loved the video. Just to note on the Mac this produced the following error: [Run-time error '9' Subscript out of range]
Sub UseAdvanceFilterInPlace()
Dim rgData As Range, rgCriteria As Range
Set rgData = ThisWorkbook.Worksheets("Data").Range("A10").CurrentRegion
Set rgCriteria = ThisWorkbook.Worksheets("Data").Range("A1").CurrentRegion
rgData.AdvancedFilter xlFilterInPlace, rgCriteria
End Sub
Just so great .. (Applause) This was just it what i was looking for. Thak you so much for the effort and everything.
Thank you for your time and expertise. Top-notch tutorials! You have a new subscriber here 👍
Welcome aboard!
hi can we use advanced filter to merge two tables using a key column?
Thank you so much for putting a lot of great videos.
Thanks a lot i do my first Macro once checking your Video, Very Helpful Video
Glad it helped
Your tuto is a filtering reference !!
Thanks for the video. It works perfectly as shown, except when I am trying to paste the filtered results to an existing table. When copying to an existing table it only copies the header rows. Is there anything we have to do differently when copying to another table ?
Hi, thank you so much for this. This is extremely useful, I was wondering how you would write the code in order to exclude data. For example, say that you want to exclude all data that includes Ann? Thanks
See here(excelmacromastery.com/vba-advanced-filter/) for a quick guide to all filtering options.
Thank you for the tutorial - great insights - Q: how can I add a wildcard (*) before the name - as you know text fields may not have one name or one word, and the word one search for in many cases is located in the middle of the text - Please let me know of the add code to do this, Thanks!
Hi, thanks for posting this! It has really helped with a problem I have been facing :-)
I have one question. Would it be possible to extend this macro so that it looped through a number of different filter criteria?
For example, could you populate row 2 and 3 with different criteria and the macro would take both sets of information and paste them in the output?
Nice l know and using advanced filter for past 20 years and named ranges and tables also very useful
Thanks for sharing
Amazing video sir... really very helpful videos you upload..🙏😊
Thank you Paul for this amazing code.
You're welcome Edmundo.
Thanks for the tutorial.
What if you're using one condition in another worksheet, and the output should be a csv saved in a specific folder without the header?
How do you check the time spent in running the macro?