It's hard to believe that Excel Advanced Filter Week is drawing to a close. I hope that the three filter feeding shark species, and you, have enjoyed this tribute to Discovery Channel's Shark Week.

很难相信Excel Advanced Filter Week即将结束。 我希望这三种过滤器可以喂食鲨鱼物种，并且您也对探索频道的“鲨鱼周”表示敬意。

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

今天，我们将看到如何在使用“高级过滤器”时记录宏，以及如何编辑该宏，这样，如果数据发生更改，它将自动进行调整。

高级过滤器结果是静态的 (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.

在此示例中，我们记录了一个宏，同时将最重要的订单过滤到了另一个工作表上。 原始数据在“订单”表上，而复制的数据在“ TopOrders”表上。

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

这是Excel宏记录器记录的代码。 我添加了一些换行符以使其更窄。

编辑高级过滤器宏 (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
  以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.

代码中的列表范围设置为“ A1：D15”。 您可以将其更改为单元格A1的CurrentRegion，因此，如果添加或删除行，则范围将自动调整。

Sheets("Orders").Range("A1").CurrentRegion

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添加工作表名称。 然后，如果目标表在运行宏时未处于活动状态，则它将仍然可以正常工作。

CopyToRange:=Sheets("TopOrders").Range("A1:D1")

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, or Macro-Enabled File Type.

注意：在Excel 2007和更高版本中，保存文件时，请使用“二进制”或“启用宏的文件类型”。

下载高级过滤器列表工作簿 (Download the Advanced Filter List Workbook)

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

要查看样本数据并测试过滤器，可以下载Advanced Filter Macro样本工作簿 。

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

该文件为Excel 2007格式，并且已压缩。 该文件包含宏，因此如果要测试代码，请启用它们。

观看高级过滤器宏视频 (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 Excel Video tutorial.

要查看设置标准范围和运行过滤器的步骤，您可以观看这段简短的Excel Video教程。

演示地址