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.