自动筛选与ShowAllData [英] AutoFilter vs ShowAllData

问题描述

_背景:

自最近以来，我开始了解通过使用AutoFilter创建的一个名为range的秘密.通常(如果不是总是这样)将其称为"_FilterDatabase".

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

本来我想过玩一点，但后来卡在了它所引用的Range对象上.让我用以下示例进行解释:

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:

_{测试数据:}

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

_{测试代码:}

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

没有结果

_问题:

如果我在上面运行宏，将不会有结果.

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

_问题:

使用.ShowAllData方法而不是.AutoFilter并运行两次代码可以解决此问题:

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

但是，.AutoFilter清除过滤器并将其从我们的范围内删除.在这两种情况下，秘密的命名范围都将保留在名称管理器下的Formulas标签中.

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.

有人知道为什么ShowAllData确实会在第二次运行中影响返回的命名范围吗?

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

推荐答案

我已经找到了我自己的问题的答案(事后看来，这似乎不符合我的发现，因此我对其进行了编辑).

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

根据我的问题，AutoFilter将立即在水下创建一个加密的命名范围，通常(如果不是始终如此)称为"_FilterDatabase".我注意到的是以下内容:

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

但是:

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

这可以解释为什么我的代码末尾的AutoFilter使得第二次它也无法正常工作.但是，由于ShowAllData不会不删除实际的过滤器(仅是标准)，因此它将在第二次运行中识别出范围A1:B6.因此，我需要做的是首先设置.AutoFilter，以使命名范围获得正确的范围.现在，以下内容可以正常工作:

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

---

_恢复:

AutoFilter立即在AutoFilter方法上创建一个秘密的命名范围.您不能直接使用条件初始化过滤器.这样做会混淆命名范围，并且现在仅引用第一行.必须按顺序使用它们！

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

这篇关于自动筛选与ShowAllData的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！