Mysterious Excel 2013 Filter Problems

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.

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.

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)

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.

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.

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?

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
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.

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.

____________________