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.

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

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.

advancedfilterslicer04

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)

advancedfilterslicer05

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.

advancedfilterslicer06

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.

Excel Dashboard Course

____________________

You may also like...

13 Responses

  1. Frank says:

    It is not a VBA problem.
    In case of a slicer, filtering is disabled in the UI as long as the active cell on the list sheet stays within the named table.

  2. Wainers says:

    Just had the same problem but with a different cause. I was pulling data from a table with AdvancedFilter and converting the extract_range (including extracted data) into a table. All working very nicely until I used criteria that returned no records. The resulting table had two rows (a header row and one empty data row – the minimum Excel allows), which made sense for what I was doing but any subsequent run with valid criteria resulted in the error message. Whilst your solution to the problem didn’t help me directly, your story about having to experiment got me experimenting too.

    After a bit of ‘trial and error’ I discovered that the problem only cropped up when my table had two rows and no data. So my simple solution was to check if autofilter returned zero records (extract_range.currentregion.rows.count=1) and, if it did, to resize my table to 3 rows instead of 2. Problem solved!

  3. Lionel says:

    Just found the same (?) problem when using – as base data – a pivot table (with column extensions – as a named range – it’s not a table; and no slicers) as the originating list.

    Error = when a cell in the base table is “active” (even when it’s not on the current active sheet).
    Fine Otherwise. Solution – per Debra (thanks!).

    Have also run into Wainers 3 row requirement problem – Advanced Filter = powerful – but very frustrating to debug….

  4. Pierre Daigneault says:

    Many thanks Debra,

    Using standard 2013 table with no slicers. Critera on different sheet and getting the problem. Hoping that your finding works for me.
    Many thanks for finding this “bug”…..I have tried for quite some time to get the advanced filter to work….checking back the active cell was on the title line of the table…….great find…..must have taken you a lot of work to find this one.

    • Richard Wright says:

      Ditto. thanks Debra. This has been bugging me for a long time. My source data is on a different sheet. When I made sure a cell on that sheet was not within the data range (I didn’t use a pivot table or a table, just a range) then it worked.

  5. Pierre Daigneault says:

    Didn’t work….now putting in private sub can calling…..works OK….also changed to named range in place of table to speed up…..

  6. Skippy says:

    I’m not using Slicers but have been experiencing the same issue regarding Advanced Filters. The Macro worked great during the initial run & then consistently failed thereafter with the error: Runtime error 1004 AdvancedFilter failed to copy…

    This conversation combined with others helped me solve my issue. There are three components to coinsider:
    1) saving after each time the macro is ran,
    2) starting & finishing the Marco with the active cell outside of the table, &
    3) clearing all copied data prior to re-running the Macro again.
    Below are my two Macros that I found to work. Both Macro finish with making cell “N10” the active cell. This cell is outside of the table.
    ________________________________________________________________
    Sub FilterMe()

    ‘ FilterMe Macro
    ‘ Advanced Filter

    ‘ Keyboard Shortcut: Ctrl+Shift+D

    Range(“A8:A10”).Select
    Selection.AutoFilter
    Sheets(“PO”).Range(“A1:K40”).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Range(“M6:Q7”), _
    CopyToRange:=Range(“a8:k8”), _
    Unique:=False
    ActiveWorkbook.Save
    Range(“N10”).Select
    End Sub
    _____________________________________________________

    Sub ClearMe()

    ‘ ClearMe Macro
    ‘ Clears data

    ‘ Keyboard Shortcut: Ctrl+Shift+C

    Range(“A9:K11956”).Select
    Selection.Clear
    ActiveWorkbook.Save
    Range(“N10”).Select
    End Sub

  7. AJ says:

    Very astute observation. I had issue of active cell. I have many slicers on my table but it does not cause an issue.
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *