自动筛选-使用SpecialCells [英] AutoFilter - Use of SpecialCells

问题描述

_背景:

我已经多次应用 AutoFilter ，从来没有真正问过自己为什么它有时会如此运作.有时，处理过滤后的数据的结果可能会造成混乱，尤其是当SpecialCells开始发挥作用时.

For many times I have applied AutoFilter and never really asked myself why it works the way it does sometimes. Working with the results of filtered data can be confusing at times, especially when SpecialCells comes into play.

让我详细说明以下情况:

Let me elaborate with the below scenario:

_{测试数据:}

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

---

_{代码1-普通AutoFilter:}

---

_{Code 1 - Plain AutoFilter:}

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

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

This will work (even without the use of SpecialCells(12)), but will populate B1.

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

_{Code 2 - Using .Offset:}

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

To prevent the above behaviour we can implement Offset like so:

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

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

However, this will now populate the row below our data, cell B7.

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

_{Code 3 - Using .Resize:}

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

To prevent .Offset to populate B7 we must now include a .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"上过滤的的.如我所见，这与以下事实有关:所引用的范围现在由零个可见单元格组成.

Allthough now we both prevented B1 and B7 to be populated we got B2:B6 populated, the AutoFilter mechanism appears to be "broken". I tried to show it with the below screenshots. The middle one is when filtered on ">30" and the right one when filtered on ">50". As I see it, this will have to do with the fact that the referenced range now consists of zero visible cells.

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

_{Code 4 - Using .SpecialCells:}

在这里，我通常要做的是先Count可见单元格(包括范围内的标头，以防止error 1004).

The normal thing for me to do here would to Count the visible cells first (including the headers in the range to prevent an error 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被覆盖.

As you can see, I went from .Columns(2).Value = "Check" all the way to If .SpecialCells(12).Count > 2 Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check", just to prevent B1 to be overwritten.

很明显，AutoFilter机制在第一种情况下可以很好地检测自身可见的行，但是为了防止标题被覆盖，我必须实现:

Apparently, AutoFilter mechanism does work very well in the first scenario to detect visible rows itself, but to prevent the header to be overwritten I had to implement:

• Offset
• Resize
• SpecialCells(12)

我在这里使事情变得过于复杂了，会不会有一条更短的路线?另外，为什么没有可见的细胞会填充整个范围的不可见细胞.当实际上有一些数据被过滤时，它将很好地工作.这是什么机制(请参见代码3)?

Am I overcomplicating things here and would there be a shorter route? Also, why does a whole range of invisible cells get populated once no cells are visible. It would work well when there is actually some data filtered. What mechanism does this (see code 3)?

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

The, not so very elegant (IMO), option I came up with is to rewrite 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

推荐答案

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

Whenever Excel creates a filtered list on a worksheet, it creates a hidden named range in the background in the Name Manager. This range is not normally visible if you call up the name manager. Use the below code to make your hidden named ranges visible in the name manager (prior to using it, set a filter on a range):

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

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

In english versions of Excel, the hidden filter range is called _FilterDatabase.My solution uses this hidden range in combination with SpeciallCells(12) to solve the problem.

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

UPDATE My final answer does not use the hidden named ranges, but I'm leaving that info as it was part of the discovery process...

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进行了测试.它按预期执行.

I tested it with >30 and >50. It performs as expected.

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