You receive an error message when you create a list from a filtered range of cells in Excel 2003 or when you create a table from a filtered range of cells in Excel 2007


Symptoms


When you try to create a list from a filtered range of cells in Microsoft Office Excel 2003, or when you try to create a table from a filtered range of cells in Microsoft Office Excel 2007, you receive the following error message:
This operation is attempting to change a filtered range on your worksheet and cannot be completed. To complete this operation, AutoFilters in the sheet need to be removed.

Cause


This problem occurs if the range in Excel is filtered and the last row of the range does not appear in the filtered range.

Workaround


To work around this problem, include the first blank row under your filtered range when you create a list.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information


If your filtered range also contains a query table, you receive the following error message first:
Your selection contains one or more query tables. Do you want to convert the selection to a list and remove all query table connections?
This error message is not part of the problem that is mentioned in the "Symptoms" section.

References


For more information about how to obtain data by using a query, click Microsoft Excel Help on the Help menu, type query table in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.


For more information about how to filter a range, click Microsoft Excel Help on the Help menu, type filter a range in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.


For more information about how to create a list, click Microsoft Excel Help on the Help menu, type create a list in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.