excel筛选器多选_神秘的Excel 2013筛选器问题

excel筛选器多选

A couple of days ago, I had problems while trying to run a simple macro in Excel 2013. Things didn’t go too well, and it took me quite a while to solve the problem. You can read the details below, and I hope it helps, if you ever run into a similar error.

几天前，我在尝试在Excel 2013中运行一个简单的宏时遇到了问题。事情进展得并不顺利，我花了相当长的时间才能解决问题。 您可以阅读以下详细信息，如果遇到类似错误，希望对您有所帮助。

I also have a couple of announcements, before you read the mystery of the misbehaving macro:

在您阅读行为异常的奥秘之前，我还发布了一些公告：

• From Oct. 8-10, Jan Karel Pieterse is offering a 50% discount on his Formula auditing tool, RefTreeAnalyser. Enter the coupon code: MVP2014

  从10月8日至10日，Jan Karel Pieterse将为其公式审核工具RefTreeAnalyser提供50％的折扣。 输入优惠券代码： MVP2014

• Mynda Treacy’s Excel Dashboard course is open for registration. Save 20% if you sign up by Oct. 16th. Enter DebraD in coupon code box.

  Mynda Treacy的Excel Dashboard课程已开放注册。 如果您在10月16日之前注册，可节省20％ 。 在优惠券代码框中输入DebraD 。

记录一个简单的宏 (Record a Simple Macro)

To make my month end reporting easier, I decided to write a simple macro, to pull some data from a large table. I set up a criteria range and extract range for an Advanced filter, so I could get the few columns that I needed, with records for a specific month.

为了使我的月末报表更容易，我决定编写一个简单的宏，以从大型表中提取一些数据。 我为“ 高级”过滤器设置了标准范围和提取范围，因此我可以获得需要的几栏以及特定月份的记录。

The easiest way to get started is by recording some of the steps, so I turned on the Macro Recorder, while applying an Advanced Filter. It created the short list that I needed, and I turned off the recorder.

最简单的入门方法是记录一些步骤，因此我在应用高级过滤器的同时打开了Macro Recorder。 它创建了我所需的简短列表，然后关闭了录音机。

简单的宏不会运行 (Simple Macro Won’t Run)

Next, I changed the criteria, and tried to run the recorded macro. Instead of working nicely, the macro showed an error. (Run-time error 1004: AdvancedFilter method of Range class failed)

接下来，我更改了条件，并尝试运行记录的宏。 宏无法正常工作，但显示错误。 (运行时错误1004：Range类的AdvancedFilter方法失败)

调查问题 (Investigating the Problem)

I checked the code, and added full references to the SalesRpt sheet, in case that was the problem. The macro still wouldn’t run.

我检查了代码，并在问题出处添加了对SalesRpt工作表的完整引用。 宏仍然无法运行。

Sub FilterSalesData()
With Sheets("SalesRpt")
  Sheets("SalesData") _
    .Range("Sales_Data[#All]") _
     .AdvancedFilter _
      Action:=xlFilterCopy, _
      CriteriaRange:=.Range("C1:D2"), _
      CopyToRange:=.Range("G1:I1"), _
      Unique:=False
End With
End Sub

I tried using named ranges for the criteria range and extract range, but still got the error. Then, I tried different ways of referencing the List Source, in case the structured reference to the table ("Sales_Data[#All]") was the problem. Nothing made a difference.

我尝试将命名范围用于条件范围和提取范围，但仍然出现错误。 然后，如果出现表的结构化引用(“ Sales_Data [#All]”)，我尝试了不同的引用列表源的方法。 没有任何改变。

卸下切片器 (Remove the Slicers)

The main table had Slicers, so maybe those were the problem.

主表上有切片器，所以也许是问题所在。

To test that theory, I created a sample file, with a little table, and no slicers, and the macro worked. I added a Slicer for one field, and the macro still worked. Was there a specific kind of field that caused problems, if a Slicer was added for that field?

为了验证该理论，我创建了一个示例文件，该文件只有一个小表，没有切片器，并且宏起作用了。 我为一个字段添加了切片器，并且该宏仍然有效。 如果为该字段添加了切片器，是否存在导致问题的特定类型的字段？

Next, I added a second Slicer, for another field, and when I ran the macro, the error appeared again. But, when I deleted that second Slicer, the error kept appearing -- most of the time. Sometimes it ran without showing the error. Frustrating!

接下来，我为另一个字段添加了第二个“切片器”，当我运行宏时，错误再次出现。 但是，当我删除第二个“切片器”时，大多数时间该错误始终出现。 有时它运行时没有显示错误。 令人沮丧！

灯泡继续点亮 (The Light Bulb Goes On)

Finally, I noticed that the macro ran correctly if the active cell was outside of the main table. Was that just a fluke, or could the problem’s solution really be that simple?

最后，我注意到如果活动单元格不在主表中，则宏可以正确运行。 只是a幸，还是问题的解决方案真的那么简单？

After testing multiple times, the macro ran correctly with the active cell outside of the table, and consistently showed the error if the active cell was inside the table. I was glad to find the solution, but annoyed that it took so long!

经过多次测试后，该宏在表外的活动单元格中正确运行，并且如果活动单元格在表内，则该宏始终显示错误。 我很高兴找到解决方案，但很烦，它花了这么长时间！

解决问题 (Fixing the Problem)

From my experiments, I figured out that the error only occurred under the following combination of conditions:

从我的实验中，我发现错误仅在以下条件组合下发生：

1. The filter is being applied via VBA
   筛选器是通过VBA应用的
2. The active cell on the List sheet is within the named table (even if that sheet is not active)
   列表工作表上的活动单元格在命名表内(即使该工作表未处于活动状态)
3. There is at least one connected Slicer.
   至少有一个连接的Slicer。

In all other situations, the macro runs without problems.

在所有其他情况下，宏运行都没有问题。

So, if you’re running a macro that applies an Advanced Filter to a table with a Slicer, make sure that the active cell is outside of the List Source table. You could add a line of code somewhere in the macro, to select a specific, non-table cell. Usually, I try to avoid selecting ranges in a macro, but in this case, I’ll make an exception.

因此，如果您正在运行使用“切片器”将高级筛选器应用于表的宏，请确保活动单元格在“列表源”表的外部。 您可以在宏中的某处添加一行代码，以选择特定的非表单元格。 通常，我会尽量避免在宏中选择范围，但是在这种情况下，我会例外。

下载样本文件 (Download the Sample File)

If you’d like to see the problem, you can download the sample file, and test the macro. This will only work in Excel 2013, because Slicers can’t be connected to named tables in earlier versions.

如果您想查看问题，可以下载示例文件并测试宏。 这仅在Excel 2013中有效，因为Slicers无法连接到早期版本中的命名表。

翻译自: https://contexturesblog.com/archives/2014/10/09/problem-with-advanced-filter-macro-and-table-slicer/

excel筛选器多选