For Excel Blackbelts: a well-hidden bug

Autofilters were the predecessors to Excel tables and are still sometimes used to filter data. An Excel bug means autofilter ranges can be misstated, hindering automated spreadsheet management.

This article looks at:

  1. Why this bug matters
  2. An Excel Blackbelt challenge: identify and solve the bug
  3. The circumstances in which this bug appears
  4. A solution
  5. Some near misses and fails

You may want to skip from 1 to 3.

Black belt - XL at side

Autofilter bug: full description, resources and solution

Rather than type lots into a text editor with output to the web page, I’ve take the route of supplying a PDF and solution spreadsheet. You can share. copy, paste etc:

The second link above is a .xlsm file with safe VBA.