excel - 自动筛选 - SpecialCells 的使用

Question

_背景:

我已经申请过很多次AutoFilter从来没有真正问过自己为什么有时会这样。使用过滤数据的结果有时可能会令人困惑，特别是当 SpecialCells 发挥作用时。

让我详细说明以下场景:

<小时/>

_{测试数据:}

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

<小时/>

_{代码 1 - 普通自动过滤:}

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        .Columns(2).Value = "Check"
    .AutoFilter
End With

这将起作用(即使不使用 SpecialCells(12))，但会填充 B1。

<小时/>

_{代码 2 - 使用 .Offset:}

为了防止上述行为，我们可以像这样实现 Offset:

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        .Columns(2).Offset(1).Value = "Check"
    .AutoFilter
End With

但是，这现在将填充我们的数据下方的行，单元格 B7。

<小时/>

_{代码 3 - 使用 .Resize:}

为了防止 .Offset 填充 B7，我们现在必须包含 .Resize:

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With

尽管现在我们都阻止了 B1 和 B7 的填充，但我们还是填充了 B2:B6，AutoFilter > 机制似乎“损坏”。我尝试用下面的屏幕截图来展示它。中间的是按 ">30" 过滤时的结果，右边的是按 ">50" 过滤时的结果。在我看来，这与引用范围现在由零个可见单元格组成这一事实有关。

<小时/>

_{代码 4 - 使用 .SpecialCells:}

我在这里做的正常事情是首先计数可见单元格(包括范围内的标题以防止错误1004)。

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        If .SpecialCells(12).Count > 2 Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With

<小时/>

_问题:

如您所见，我从 .Columns(2).Value = "Check" 一直到 If .SpecialCells(12).Count > 2 Then .Columns( 2).Offset(1).Resize(5, 1).Value = "Check"，只是为了防止B1被覆盖。

显然，AutoFilter 机制在第一个场景中确实可以很好地检测可见行本身，但为了防止 header 被覆盖，我必须实现:

• Offset
• Resize
• SpecialCells(12)

我是不是把事情搞得太复杂了，还有更短的路线吗？另外，为什么一旦没有可见的单元格就会填充整个范围的不可见单元格。当确实有一些数据被过滤时，它会很好地工作。这是什么机制(参见代码 3)？

我想出的不太优雅(IMO)的选项是重写 B1:

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        Var = .Cells(1, 2): .Columns(2).Value = "Check": .Cells(1, 2) = Var
    .AutoFilter
End With

Answer 1

每当 Excel 在工作表上创建筛选列表时，它都会在名称管理器的后台创建一个隐藏的命名范围。如果您调用名称管理器，此范围通常不可见。使用以下代码使隐藏的命名范围在名称管理器中可见(在使用它之前，在范围上设置过滤器):

Dim nvar As Name
For Each n In ActiveWorkbook.Names
    n.Visible = True
Next

在英文版 Excel 中，隐藏的筛选范围称为 _FilterDatabase。我的解决方案使用此隐藏范围与 SpeciallCells(12) 结合来解决问题。

更新我的最终答案不使用隐藏的命名范围，但我保留该信息，因为它是发现过程的一部分......

Sub test1()
Dim var As Range
Dim i As Long, ans 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

我用 >30 和 >50 对其进行了测试。它的性能符合预期。