CHAPTER 11Data 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 Record 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 ...

Get Microsoft Excel 2019 VBA and Macros, First Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.