excel - 自动筛选与ShowAllData

背景：

从最近开始，我就知道了一个使用AutoFilter创建的秘密命名范围。通常（如果不是始终如此）将其称为“ _FilterDatabase”。

我本来想稍微玩一下，但后来被卡在它引用的Range对象上。让我用以下示例进行说明：



测试数据：

| 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

没有结果



问题：

如果我在上面运行宏，将不会有任何结果。



题：

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

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选项卡中。

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

最佳答案

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

根据我的问题，AutoFilter将立即在水下创建一个加密的命名范围，通常（如果不是总是）称为"_FilterDatabase"。我注意到以下内容：

.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，让命名范围选择正确的范围。现在，以下内容可以正常工作：

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

因此，在逻辑上不起作用的是：

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

有趣的是，这现在将不需要预先知道最后使用的行来创建范围对象（但是，由于数据中的空白会丢弃指定的范围，因此人们可能仍然更喜欢该方法）。