Instead of manually running an Advanced Filter, save time with a macro. Set up a criteria area and extract area, then run a macro to filter the results. Results can show in the list, by hiding rows, or you can send the results to a different sheet
An Advanced Filter can show specific data from a list, by applying criteria. Follow the instructions in the Advanced Filter Introduction page, to see how to set up a Criteria Range, and an Extract Range.
In the screen shot below, there is a criteria range in cells B1:D2, using headings that match columns in the table below.
The green "Filter" button will run a filter macro, to send the matching records to a location on another worksheet.
The easiest way to get started with Advanced Filter programming is to record a macro, while you manually apply a filter. Watch this video to see the steps, and the revised code is shown below.
There are instructions here for recording and running a macro.
In this example, a macro was recorded while running an Advanced Filter to copy the top orders onto a different worksheet.
Here is the code that was recorded by the Excel macro recorder.
After you record the macro, you can edit the recorded code, to make it flexible, and to remove any unnecessary lines of code.
The list range in the recorded code is set as "A1:D15". That was changed to Range("A1").CurrentRegion, so the range will automatically adjust, if rows are added or removed.
The other key change was to add the sheet name -- Sheets("Orders") -- for the CopyToRange. Then, if the destination sheet is not active when the macro runs, it will still work correctly.
A few unnecessary lines were also deleted, such as the comment lines, that start with an apostrophe, and the lines that select a cell.
Here is the code, after making a few changes to it, as shown in the video. A few extra line breaks were added, so the code would fit better on this page.
Sub TopOrderFilter() Sheets("Orders").Range("A1") _ .CurrentRegion.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Orders") _ .Range("F1:F2"), _ CopyToRange:=Sheets("TopOrders") _ .Range("A1:D1"), _ Unique:=False End Sub
In Excel 2013, you can connect a Slicer to a formatted table. A table's heading row automatically has filter arrows added, but you can still use an Advanced Filter to pull data from the table.
However, if there is a Slicer connected to the table, and the active cell is within the formatted table, an Advanced Filter macro won't run -- it will show an error message.
Make sure that the active cell on the formatted table's sheet is outside of the table, before you run the advanced error.
To see the problem, you can download the "Problem with table slicers" sample file, from the Download section, below.
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Last updated: November 17, 2016 3:36 PM
Contextures RSS Feed