Clearing The Advanced Filter Dialog Box
When you choose the Data, Filter, Advanced Filter command, Excel displays its Advanced Filter dialog box. You've probably noticed that Excel "remembers" the previous range specifications for the List range, Criteria range, and Copy to range. In most cases, this is useful. But if you're working with several different worksheet databases, you may prefer that Excel uses the current database rather than the previous database.
There is no direct way to clear the previous settings from the Advanced Filter dialog box. However, you can use the simple VBA procedure listed below.
Sub ShowAdvancedFilterDialog() ' Delete names On Error Resume Next With ActiveWorkbook .Names("_FilterDatabase").Delete .Names("Criteria").Delete .Names("Extract").Delete End With On Error GoTo 0 ' Display the dialog box Application.Dialogs(xlDialogFilterAdvanced).Show End Sub
How it works
Excel keeps track of the previous Advanced Filter range specifications by using three name: _FilterDatabase, Criteria, and Extract. The ShowAdvancedFilterDialog procedure simply deletes these names, and then uses the Show method to display the Advanced Filter dialog box. Because these names are not defined, it's as if you are using the Advanced Filter command for the first time.
Note: The _FilterDatabase name is a hidden name. Therefore, the only way to delete it is by using VBA code. The Criteria and the Extract names are normal names, and can be deleted using the standard Define Name dialog box.
Using the procedures
To use this procedure, copy it and paste it to a VBA module in your workbook.
Then, execute the ShowAdvancedFilterDialog macro instead of the Data, Filter,
Advanced Filter command.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks