Search code examples
excelvbaautofilternamed-ranges

AutoFilter vs ShowAllData

Background:

Since recently I came to know about a secret named range that gets created through using AutoFilter. Usually (if not always) this is called "_FilterDatabase".

I thought about playing around with this a little, but then got stuck on the Range object it refers to. Let me explain with the example below:

Test data:

| Header1 | Header2 |
|---------|---------|
| 50      |         |
| 10      |         |
| 30      |         |
| 40      |         |
| 20      |         |

Test Code:

Sub Test()

With Sheet1
    .Range("A1:B1").AutoFilter 1, ">40"
    Dim rng As Range: Set rng = .Range("_FilterDatabase")
    If rng.SpecialCells(12).Count > 2 Then
        rng.Columns(2).Offset(1).Resize(rng.Rows.Count - 1, 1).Value = "Check"
    End If
    .Range("A1:B1").AutoFilter
End With

End Sub

With no results

Issue:

If I would run the macro above there would be no results.

Question:

The issue is resolved using .ShowAllData method instead of .AutoFilter and running the code twice:

Sub Test()

With Sheet1
    .Range("A1:B1").AutoFilter 1, ">30"
    Dim rng As Range: Set rng = .Range("_FilterDatabase")
    If rng.SpecialCells(12).Count > 2 Then
        rng.Columns(2).Offset(1).Resize(rng.Rows.Count - 1, 1).Value = "Check"
    End If
    .ShowAllData
End With

End Sub

However, .AutoFilter clears the filter and removes it off from our range. In both cases the secret named range will remain in the Formulas tab under names manager.

Does someone have any idea why ShowAllData does affect the returned named range on the second run?

Solution

  • I have found the answer to my own question (which hindsight, does not seem to comply with my findings and therefor I edited it).

    As per my question, AutoFilter will immediately create a secred named range under water, usually (if not always) called "_FilterDatabase". What I noticed is the following:

    .Range("A1:B1").AutoFilter 1, ">40"  '-> Named range will refer to A1:B1

    However:

    .Range("A1:B1").AutoFilter '-> Named range will refer to A1:B1
.Range("A1:B1").AutoFilter 1, ">40" '-> Named range will refer to A1:B6

    This would explain why AutoFilter at the end of my code makes that the second time it wouldn't work correctly either. However, since ShowAllData does NOT remove the actual filter (just the criteria) it will on the second run recognize the range A1:B6. Therefor, what I needed to do is to set .AutoFilter first to let the named range pick up the correct range. Now the following works correctly:

    Sub Test()

With Sheet1
    .Range("A1:B1").AutoFilter
    .Range("A1:B1").AutoFilter 1, ">40"
    Dim rng As Range: Set rng = .Range("_FilterDatabase")
    If rng.SpecialCells(12).Count > rng.Rows(1).Cells.Count Then
        rng.Columns(2).Offset(1).Resize(rng.Rows.Count - 1, 1).Value = "Check"
    End If
    .Range("A1:B1").AutoFilter
End With

End Sub

    What would therefor, logically not work is something like:

    .Range("A1:B1").AutoFilter '-> Named range will refer to A1:B1
Set rng = Set rng = .Range("_FilterDatabase")
rng.AutoFilter 1, ">40" '-> rng still refers to A1:B1

    Resume:

    AutoFilter creates a secret named range on the AutoFilter method instantly. You cannot initialize the filter with a criteria directly. Doing so confuses the named range and will now only refer to the first row. They have to be used in sequence!

    The fun part is that this would now remove the need to know the last used row to create a range object beforehand (however, one may still prefer that method, since a gap in the data will throw the named range off).