Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)

Chapter 11

Data mining with Advanced Filter

In this chapter, you will:

• Replace a loop by using AutoFilter

• Get to know Advanced Filter

• Use Advanced Filter to extract a unique list of values

• Use Advanced Filter with criteria ranges

• Use Filter In Place in Advanced Filter

• Use Advanced Filter to return all records that match the criteria

Read this chapter.

Although very few people use Advanced Filter in Excel, it is a workhorse in Excel VBA. I estimate that I end up using one of these filtering techniques as the core of a macro in 80% of the macros I develop for clients. Given that Advanced Filter is used in fewer than 1% of Excel sessions, this is a dramatic statistic.

So even if you hardly ever use Advanced Filter in regular Excel, you should ...