Removing FilterDatabase Ranges from Excel Workbooks

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Removing FilterDatabase Ranges from Excel Workbooks
If you've ever encountered a strange items called FilterDatabase when importing data from an Excel workbook, this blog explains how to get rid of them!

Posted by Andrew Gould on 20 June 2025

In this blog

The Problem
Managing Named Ranges
Using VBA to list all named ranges
Removing range names

The Problem

Recently, I was importing data from an Excel workbook into a Power Pivot data model. The workbook contained 16 worksheets and I wanted to import them all, but I spotted a few odd entries in the list of tables.

Importing Excel worksheets into a Power Pivot data model

I want to check the top box to select all the tables, but what are all these xlnm#_FilterDatabase items?

It turns out that these are special named ranges left behind after applying filters to the worksheets using Excel's AutoFilter. Each name refers to a filtered version of the worksheet - I definitely didn't want to import these!

Managing Named Ranges

Editing named ranges in Excel is normally a simple process: from the Excel ribbon choose Formulas | Name Manager to see a list of all the range names in the workbook. You can then edit or delete a named range in a fairly obvious way. When I did this in the workbook I was importing the data from, however, this is what I saw:

A list of named ranges in a Microsoft Excel workbook

I created these three named ranges manually; there's no sign of the FilterDatabase names here!

The offending names were nowhere to be seen, so where to look next?

Using VBA to list all named ranges

As I so often do when trying to solve an awkward Excel problem, I resorted to using VBA to deal with the recalcitrant range names.

To begin, I opened the Visual Basic Editor by pressing Alt + F11 and then, from the VBE menu I selected Insert | Module to create a module in which to write my code. You can learn more about VBA in our online tutorial or videos if you're interested!

Inserting a VBA module into an Excel VBA project

You'll need a module in which to write your code.

Next, I created a procedure to list the names and cell references of all the range names in the workbook. Here's the code I wrote with some comments (in green) to help explain what's going on:

A VBA subroutine to list the named ranges in an Excel workbook

This simple program will list information about every range name in the workbook.

I ran the code shown above by pressing F5 on the keyboard and, to make sure I could see the results I displayed the Immediate window by choosing View | Immediate window from the VBE menu. Here are the results:

The output of a VBA subroutine displayed in the Immediate window of the Visual Basic Editor

Found you!

Removing range names

Having found the offending names, next I needed to delete them. The complication was that I didn't want to delete the names I had created deliberately. To do this, I wrote another VBA routine to loop through the collection of names and delete any whose name contained FilterDatabase. Here's the code I used:

A VBA subroutine to delete range names from an Excel workbook

The combination of Like operator and * wildcards finds names which contain FilterDatabase.

After running this procedure, I ran the original subroutine to list out all the names in the workbook. Here are the results:

A list of named ranges displayed as the output of a VBA subroutine

My personal range names are safe and sound.

Finally, I was able to import all the tables from the workbook into my Power Pivot data model without having to tick the box for each individual table!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post