A Note of Caution When Using FilterDatabase

 

One of my favorite shortcuts when using a single-sheet, filter-enabled XLSX file as a data source is to use the FilterDatabase "sheet" instead of the actual sheet on which the data resides. This isn't really a sheet, of course, but a hidden database that Excel automatically creates whenever a workbook has a sheet with drop-down filters enabled. I like using this DB as a source because it lets me be lazy and not have to worry about changing sheet names. A worksheet may get renamed, but FilterDatabase will always be called FilterDatabase. You also don't have to worry about "garbage" header rows that don't contain meaningful data, as those won't exist in FilterDatabase.

 

Recently, however, I stumbled on an "undocumented feature" inherent with FilterDatabase. At one of my clients, I came across an anomaly that had me scratching my head for a while. This is what I saw when looking at the worksheet itself as a source:

 

But when I switched to FilterDatabase, the last row was missing:

 

What I eventually discovered was that if you do use FilterDatabase, make sure that creating column filters is the very last thing you do before you save the workbook. That's because FilterDatabase gets created at the time that you press the "Filter" button in Excel; any values you enter into the worksheet afterwards will not be included in FilterDatabase. Of course, we are often not the owners of the spreadsheets that our applications consume, so would not (or should not) save them at all. The moral of the story is that unless you're positive that the spreadsheet owner did not enter any values after enabling filters, you're probably better off avoiding FilterDatabase altogether.