Excel:具有两个以上条件的自动筛选例外

问题描述

这有时会出现一个问题(例如:在此处 和此处))并且几乎总是基于某些循环(使用 AutoFilter()本身或数组)来提供解决方案

This is a question that sometimes arises (e.g.: see here and here) and has almost always been given solutions based on some looping (with AutoFilter() itself or arrays)

由于某些原因，Excel开发人员将 AutoFilter 自定义数组条件的功能限制为最多两个，因此以下操作将不起作用:

For some reason Excel developers limited AutoFilter custom array criteria functioning to a maximum of two, so the following would not work:

    filterNotCriteria = Array("<>A","<>B","<>C")

    someRange.AutoFilter field:=1, Criteria1:=filterNotCriteria, Operator:=xlFilterValues

尽管我认为完全具有此 AutoFilter()功能会很有帮助，因为它是针对非自定义"功能的

While I think it'd be really helpful to have this AutoFilter() functionality fully available as it is for its "not custom" counterpart

因此，在等待Excel开发人员添加(或修复它，因为它在我看来更像是个错误)的同时，我将以公开调查的方式开始这个问题(不确定这是否合适)从性能和可用性的角度来看，什么可能是最好的VBA修复

So, while awaiting for Excel developers to add it (or fix it, since it looks to me more like a bug), I'd start this question as a public survey (not sure if this is the appropriate way) on what could be the best VBA fix, both from the performance and usability point of view

我正在添加第一个答案以开始滚球

And I'm adding the first answer to start the ball rolling

推荐答案

我的第一个想法是坚持使用 AutoFilter()，以从其性能中受益

My first thought was to stick to AutoFilter(), to benefit from its performance

使用一些逆向思维，步骤如下:

• 具有相应NOT(条件)的过滤器

• filter with corresponding NOT(criteria)

以便获得我们不需要的东西

so as to get what we don't need

隐藏我们获得的行，并且不需要

hide the rows we got and that we don't need

离开需要的"行

如下:

Option Explicit

Function AutoFilterNot(rngToFilter As Range, fieldToFilterOn As Long, filterNotCriteria As Variant) As Range
    Dim notRng As Range ' helper range variable

    With rngToFilter ' reference wanted range to filter, headers row included
        .AutoFilter field:=fieldToFilterOn, Criteria1:=filterNotCriteria, Operator:=xlFilterValues ' filter on "not wanted" values
        If Application.Subtotal(103, .Resize(, 1)) > 1 Then ' if any filtered cell other than header row
            Set notRng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) ' temporarily set 'myRng' to "not wanted" rows
            .Parent.AutoFilterMode = False ' remove filters and show all rows
            notRng.EntireRow.Hidden = True ' leave "wanted" rows only visible

            Set AutoFilterNot = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible) ' get referenced range "wanted" rows

            .EntireRow.Hidden = False ' unhide all referenced range rows
        Else
            .Parent.AutoFilterMode = False ' remove filters
        End If
    End With
End Function

并且可以在某些主要"代码中使用，如下所示:

and that could be used in some "main" code as follows:

Dim filteredRng As Range

Set filteredRng = AutoFilterNot(Range("A1:C200"), 2, Array("B102", "A107"))

这篇关于Excel:具有两个以上条件的自动筛选例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！