Contextures

Advanced Filter Programming

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

Advanced Filter Setup

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.

advanced filter with criteria range

Record and Edit Advanced Filter Macro

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.

Record an Advanced Filter Macro

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.

  • The original data is on the Orders sheet
  • The copied data is on the TopOrders sheet.

Here is the code that was recorded by the Excel macro recorder.

code from macro recorder

Edit the Advanced Filter Code

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

Problem With Table Slicers

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.

advanced filter with table slicer

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.

advanced filter with table slicer

Download the Sample File

  1. Problem with table slicers sample file. The zipped file contains macros, so enable macros to test the filter, and see the problem with connected slicers.

Get All the Excel News

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.

Related Tutorials

Advanced Filter Intro

Advanced Filter Criteria

Advanced Filter Criteria Slicers

Advanced Filter Macro

Excel Slicers Intro

Create Excel Tables

Search Contextures Sites

 

Excel Tools Add-in

 

 

Excel Data Entry Popup List

 

 

 

Last updated: July 10, 2017 7:35 PM
Contextures RSS Feed