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?

Qoph

Follow-up question: oooooh :) You should write a book on AutoFilter when you're done. – Huge

.Range("_FilterDatabase") is being set when the filter is applied. Removing the AutoFilter seems to have no effect . – Fabri

@Bigben, I have now understood at least this part of my AutoFilter quest =) – Qoph

Answer 1

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

Qoph

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 :- ) – Ockeghem

@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. – Qoph

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

@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. – Qoph

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

Let us continue this discussion in chat. – Qoph

Sounds good. Will only be able to do that at home though. Will write once I can. – Ockeghem

@rohrl77, I have posted your problem in chat, and therefor edited the code slightly to prevend such mishaps and do the count more dynamically – Qoph

Answer 2

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

Ockeghem

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 =) ++ – Qoph

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

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