VBA Advanced Filter - The FASTEST way to Copy and Filter Data

The Advanced Filter is the fastest way to copy and filter data in Excel VBA.

And that's not all...

It requires less code that any other method.
It requires no extra code to select or sort columns - all the other methods require a lot of extra code to do this.
It automatically formats the destination cells to match the source. If you use other methods then you need to create extra code to do this.

In this video this video I am going to show you how to use the VBA Advanced filter.
Then I am going to show you the VBA code that you need to use the Advanced Filter.
Finally I am going to show you how to connect the Advanced Filter to a worksheet event so your users(or you!) can automatically use the filter.

Related links:

Shortcut Keys:
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + R: View the Project Properties Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.
Tab: To move lines of code to the right(Indent)
Shift + Tab: To move lines of code to the left(Outdent).

Table of Contents:

00:00 - How advanced filter works
00:03 - Why use the Advanced Filter
02:59 - The VBA Code
11:37 - Using the worksheet event to filter

[LordBransty]

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!

[EOO-Stand]

This makes coding so simple, easy to read and makes the size of the files smaller. awesome. keep up the good work.

[genechicago]

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!

[donaldturrell]

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.

[angs]

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!

[davidrhodes]

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!

[soup]

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

[dangelorrrr]

There is always at least one thing I didn't know in each of your videos! Thanks again Paul, for sharing your knowledge

[bestscenes]

This is the best advanced filter tutorial I have seen in years. Excellent Presentation.

[sasavienne]

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.. 🌟

[tomjun]

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.

[kolinsin]

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)

[greg]

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")

[Nigromancy]

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!

[lindalowney]

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.

[pshamrock]

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.

[BenLinfordUK]

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 YouTube (and I believe that's a huge compliment).

[iincitr]

Thank you for your clean explanation of the subjects.

[telljansiddiqi]

Amazing THANK YOU Marco!!!!
Trust me you made my day by this video

[buithitrami]

Hi Paul! Just watching a half of this video but it is really awesome one. Never knew that advanced filter was so useful. 👍🏾