Clearing The Advanced Filter Dialog Box

Category: General VBA | [Item URL]

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.