AutoFilter vs ShowAllData

Question

_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?

Follow-up question: oooooh :) You should write a book on `AutoFilter` when you're done. — BigBen, Nov 21 '19 at 19:10
`.Range("_FilterDatabase")` is being set when the filter is applied. Removing the `AutoFilter` seems to have no effect . — TinMan, Nov 21 '19 at 19:15
@Bigben, I have now understood at least this part of my `AutoFilter` quest =) — JvdV, Nov 22 '19 at 11:05

JvdV · Accepted Answer · 2019-11-28T08:27:39.977

2

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

edited Nov 28 '19 at 08:27

answered Nov 22 '19 at 11:05

JvdV

70,606
8
39
70

I'm sorry to say it, but this isn't it either. I just tried this solution and expanded the AutoFilter range to `A1:C1`... it then ends up filling the entire column with "Check" if I set the filter criteria to >50. But I think we are closer than ever to the holy grail :- ) – rohrl77 Nov 26 '19 at 08:46
@rohrl77, thank you for the feedback. I'll have to look into that. Have you at least used a header in C1? So I can reproduce what you did. – JvdV Nov 26 '19 at 10:05
Yup. C1 had a header. I'm trying to find a solution to your other question and everytime i think i have it, a new problem pops up. Try also testing using <30, etc. – rohrl77 Nov 26 '19 at 10:07
@rohrl77, I cannot reproduce the above. Have you made sure that you also changed `If rng.SpecialCells(12).Count > 2 Then` to `If rng.SpecialCells(12).Count > 3 Then`. I still think the above answer is correct. The `<30` also does exactly what it is supposed to do. – JvdV Nov 26 '19 at 11:59
I just set it up new in a fresh workbook. Copied in your code. Changed the range to `A1:C1`. Added the header text. When I run >50 it fills all cells. Admitedly, the <30 now tests correct... not sure what happened. – rohrl77 Nov 26 '19 at 12:10
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/203104/discussion-between-jvdv-and-rohrl77). – JvdV Nov 26 '19 at 12:21
Sounds good. Will only be able to do that at home though. Will write once I can. – rohrl77 Nov 26 '19 at 12:24
@rohrl77, I have posted your problem in chat, and therefor edited the code slightly to prevend such mishaps and do the count more dynamically – JvdV Nov 28 '19 at 08:28

score 1 · Answer 2 · answered Nov 26 '19 at 12:18

1

This filters correctly without using the AutoFilter.Range:

Sub test2()
Dim var As Range
Dim i As Long
With Sheets("Sheet1").Range("A1:C1")
    .Range("B2:B6").Clear
    .AutoFilter
    .AutoFilter 1, ">50"
        Set var = Sheet1.AutoFilter.Range
        Set var = Intersect(var.SpecialCells(12), var.Offset(1, 0))
        If Not (var Is Nothing) Then
            For i = 1 To var.Areas.Count
                var.Areas(i).Offset(0, 1).Resize(var.Areas(i).Rows.Count, 1).Value = "Check"
            Next i
        End If
    .AutoFilter
End With
End Sub

answered Nov 26 '19 at 12:18

rohrl77

3,277
11
47
73

Thanks for this, but the essence of the question is answered. I was questioning why `.AutoFilter 1, ">50"` alone wouldn't work. Only on a second run with `.ShowAllData`. The solution I explained below > The use of a seperate `.AutoFilter` to initialize the named range. Furthermore, I don't think looping over `Areas` is a better way than to make use of the `AutoFilter.Range`. Thanks for the contribution though =) ++ – JvdV Nov 26 '19 at 12:27
I agree that looping over areas is less elegant. So far though, it is the only version i managed to write that consistently delivers the desired result. Perhaps we can exchange our test files in the chat and see... thanks for the question and upvote in anycase. i learned quite a bit getting at an answer myself. – rohrl77 Nov 26 '19 at 12:41
Yes, your answer is derived from the points mentioned by you under my answer. We will have to see what has conflicted in your case that made `AutoFilter` populate the whole range. I'm curious to know. I'll be on for the next couple of hours. Hit me up in chat. About upvoting: I upvote all posts when they seem to try help answer a question. It's just a decent thing to do to return the favor. – JvdV Nov 26 '19 at 12:44

AutoFilter vs ShowAllData

2 Answers2

Linked