Excel 2016 VBA and Macros

11. Data Mining with Advanced Filter

In This Chapter

Replacing a Loop with AutoFilter

Advanced Filter—Easier in VBA Than in Excel

Using Advanced Filter to Extract a Unique List of Values

Using Advanced Filter with Criteria Ranges

Using Filter in Place in Advanced Filter

The Real Workhorse: xlFilterCopy with All Records Rather Than Unique Records Only

Next Steps

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