Excel Filter Macro: Shark Week

It’s hard to believe that Excel Advanced Filter Week is almost over. I hope that the three filter-feeding shark species, and you, have enjoyed this tribute to Discovery Channel’s Shark Week.

Today, we’ll see how to record a macro while using the Advanced Filter, and edit that macro, so it automatically adjusts if the data changes.

Watch the Advanced Filter Macro Video

To see the steps for setting up the criteria range, and running the filter, you can watch this short video. The written steps are below the video.

Advanced Filter Results are Static

When you run an Advanced Filter, and copy data to a different location, the copied data is not linked to the original data.

If the original data changes, the copied data won’t automatically update. You could run the Advanced Filter again, to update the copied data.

If you plan to update the original data frequently, you can save time and effort, by creating a macro to run the Advanced Filter.

Record the Advanced Filter Steps

In this example, we recorded a macro while filtering the top orders onto a different worksheet.

The original data is on the Orders sheet, and the copied data is on the TopOrders sheet.

Here is the code that was recorded by the Excel macro recorder. I added a few line breaks to make it narrower.

Edit the Advanced Filter Macro

After you record the macro, you can edit the code, to make it flexible, and to remove any unnecessary lines of code.

In the recorded code, you can delete the following:

• comment lines, that start with an apostrophe.
• lines that end with Select

The list range in the code is set as “A1:D15”. You can change that to the CurrentRegion for cell A1, so the range will automatically adjust, if rows are added or removed.

Sheets(“Orders”).Range(“A1”).CurrentRegion

The other change you should make is to add the sheet name for the CopyToRange. Then, if the destination sheet is not active when you run the macro, it will still work correctly.

CopyToRange:=Sheets(“TopOrders”).Range(“A1:D1”)

The Edited Advanced Filter Macro

Here is the final version of the edited Advanced Filter macro.

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

Note: In Excel 2007 and later, when you save the file, use the Binary file type (xlsb), or Macro-Enabled File Type (xlsm).

Download the Advanced Filter List Workbook

To see the sample data, and test the filter, you can download the Advanced Filter Macro sample workbook.

The Excel sample file is in xlsm format, and is zipped. The file contains macros, so enable them if you want to test the code.

___________________