How to use AND, OR and Formulas with VBA Advanced Filter

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

In a previous video, I show that Advanced Filter is the fastest and most efficient way to copy and filter data using Excel VBA.

In this video, I show you how to unlock the power of the Advanced Filter using AND and OR in the criteria.

We also look at the little-known method of using an Excel formula in the criteria.

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 - Introduction
01:06 - Using AND criteria
01:47 - Using OR Criteria
02:46 - Using Formulas in Criteria
05:03 - Using Wildcards

[Excelmacromastery]

Hi everyone,


In this video, I show you how to unlock the power of the Advanced Filter using AND and OR in the criteria.
If you have any questions, please leave your comments below.

A quick guide:
T* - all cells that start with T.
T - all cells that start with T.
*Y - all cells that end with Y.
="=Tom" - all cells that match exactly Tom.
="<>Tom" - all cells that do not match exactly Tom.

[ganlyb]

To filter between two dates, you need an AND. In the criteria range enter to columns with the name and the dates. NB tested with >01-Jan-2019, not number only dates, so American formatting may mix things up, Filter for a year

Day Day
>=01/01/2019 <01/01/2020

[wayneedmondson]

Hi Paul.. another useful and informative video. Learning a lot about both VBA and basic use of native Advanced Filter.. excellent! Thanks and keep them coming. Love your videos, tutorials, blog posts, web site, etc. Excel Macro Mastery is the place to go to learn it right. Thumbs up!!

[swastikhotmail]

Greatly appreciating your videos. Lot of learning. Can you please create a video showing Date field and explain how to extract data between two dates with all combinations. I would really look forward to see that.

[sasavienne]

I really liked these videos of using the advanced filter in VBA. This tutorial is very useful and helpful. Thanks indeed Paul.

[joaocustodio]

Paul, today I have watched / I reviewed some of your videos and always learned something new. Thank you very much.

[buithitrami]

Thanks for this video Paul. I’ve learned a lot from it.

[eddai]

Thank you so much, Paul. It's very brilliant.

[JohnOvens]

Excellent Paul - the Criteria is similar to ACCESS queries.

[Jack_C_]

Thanks Paul, immensely useful and very well presented.
Never new about using a formula with Advanced Filter, makes it even more powerful than I thought.
Would love to see some advanced stuff on web scraping with VBA so I can get the data set in the first place.
Please keep the videos coming.
:)

[gnfielies]

Thank you very much, an I hope to see more. You are amazing, great teaching skill.

[grahamparker]

Just found your channel Paul. Loving it, keep up the great work 👍🏻

[alihamiad]

Very nice! That is what I'm looking for.

[amarnadhg]

Great video, plz keep it doing regular

[nielskock]

Love the content, as a suggestion for another video. I would love to see how or if you can use conditional formatting in combination with Advanced filters and how that is done in VBA. To for example highlight fields that include Tom yellow and sales above 20.000 in Green etcs.

[imranbhatti]

Hey Paul nice to see you on youtube. I didn't know that you have a youtube channel. I just knew about your website.

[abeibrahim]

how to use RegEXtext in advance search to allow searching for embedded text?

[minhcuong]

hi a thank you for sharing the channel to help me e with a

[ganeshs]

very informative. In my case there are over 200 columns of data and need to filter on few columns, and the output should be generated with few specific columns and not all 200. is this possible with advanced filter?

[andrewscoins]

Could you Show ist how to use IT in Userform with Text or Comboboxen?