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.


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

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

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy