基于数组的自动筛选，超过 3 个元素 [英] AutoFilter based on an array, more than 3 elements

问题描述

我的变量包含通配符.我在网上看到说如果我使用通配符，自动过滤器只能包含两个条件.这是真的?如果是这样，这很可能是我的问题.不幸的.结束编辑

My variables contain wildcards. I saw something online that says that if I am using wildcards, the autofilter can contain only two conditions. Is this true? If so, this is likely my issue. Unfortunate. END EDIT

我正在尝试过滤我的数据以显示数据是否包含数组中的六个项目之一.元素是字符串变量.我的工作代码是:

I am trying to filter my data to show if the data contains one of the six items in an array. The elements are String variables. My working code is:

With ActiveSheet
    .Columns("J").AutoFilter Field:=1, Criteria1:=Array(d3, d2), _
     Operator:=xlFilterValues
End With

这背后的想法适用于我希望完成的事情.但是，我实际上想要数组的 6 个元素，而不仅仅是两个.不幸的是，当我尝试添加所有六个元素时，没有任何显示.

The idea behind this works for what I wish to accomplish. However, I actually want 6 elements of the array, not just two. Unfortunately, when I try to add all six elements, nothing shows up.

 With ActiveSheet
    .Columns("J").AutoFilter Field:=1, Criteria1:=Array(d3, d2, d1, d21, d11, d31), Operator:=xlFilterValues
End With

我没有收到错误或任何信息.只是什么都没有出现.有谁知道如何解决这个问题?我已经用数组中的两个元素和字符串的各种组合(d1、d21 等...)测试了代码，它们都按预期工作，所以问题不在于变量.

Im not getting an error or anything. It's just that nothing shows up. Does anyone know how to fix this issue? I've tested the code with just two elements in the array with various combinations of the Strings (d1, d21, etc...) and they all work as intended, so the issue is not with the variables.

推荐答案

AutoFilter 仅限于 2 个带通配符(* 或 ?)的条件

AutoFilter is limited to 2 criteria with wildcards (* or ?)

以下 2 个版本将允许您根据需要指定任意数量的通配符

The 2 versions below will allow you to specify as many wildcards as you need

.

版本 1 - 循环使用 AutoFliter 并组合可见范围的每个通配符

Version 1 - Loops through each wildcard applying the AutoFliter and combining the visible ranges

Option Explicit

Public Sub FilterRows3WildAF()      '(Optional ByVal showAll As Boolean = False)

    Const FILTER_COL = "A"
    Const WILDCARDS = "Name Street Address Number"  'cell starts with these 4 words

    Dim ws As Worksheet, wild As Variant, lr As Long, toShow As Range, itm As Variant

    Set ws = ActiveSheet
    wild = Split(WILDCARDS) 'will search for cells starting with: Name*, then Street*, etc
    Application.ScreenUpdating = False
    ws.Rows.Hidden = False

    With ws.Range(ws.Cells(1), ws.Cells(ws.Rows.Count, FILTER_COL).End(xlUp))
        lr = .Rows.Count
        Set toShow = .Cells(lr + 1, FILTER_COL)
        For Each itm In wild
            .AutoFilter Field:=1, Criteria1:=itm & "*", Operator:=xlFilterValues
            If .SpecialCells(xlCellTypeVisible).Cells.CountLarge > 1 Then
                Set toShow = Union(toShow, .Offset(1).SpecialCells(xlCellTypeVisible))
            End If
        Next
        .AutoFilter
        .Rows.Hidden = True
        toShow.EntireRow.Hidden = False
    End With
    Application.ScreenUpdating = True
End Sub

<小时>

.

版本 2 - 遍历每个单元格，使用 InStr() 检查通配符是否存在

Version 2 - Loops through each cell, checking with InStr() if the wildcard exists

Public Sub FilterRows3WildInstr()   '(Optional ByVal showAll As Boolean = False)

    Const FILTER_COL = "A"
    Const WILDCARDS = "Name Street Address Number"  'cell starts with these 4 words

    Dim ws As Worksheet, wild As Variant, lr As Long, arr As Variant
    Dim toHide As Range, r As Long, itm As Variant, found As Boolean

    Set ws = ActiveSheet
    wild = Split(WILDCARDS) 'will search for cells starting with: Name*, then Street*, etc
    ws.Rows.Hidden = False

    With ws.Range(ws.Cells(1), ws.Cells(ws.Rows.Count, FILTER_COL).End(xlUp))
        lr = .Rows.Count
        arr = .Value2
        Set toHide = .Cells(lr + 1, FILTER_COL)
        For r = 1 To UBound(arr)
            For Each itm In wild
                found = InStr(1, arr(r, 1), itm) > 0
                If found Then Exit For
            Next
            If Not found Then Set toHide = Union(toHide, .Cells(r, FILTER_COL))
        Next
        toHide.EntireRow.Hidden = True: .Rows(lr + 1).Hidden = False
    End With
End Sub

这篇关于基于数组的自动筛选，超过 3 个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！