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

查看:103
本文介绍了基于数组的自动过滤,超过3个元素的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的变量包含通配符.我在网上看到一些内容,说如果我使用通配符,则自动筛选器只能包含两个条件.这是真的?如果是这样,这可能是我的问题.不幸的. 结束编辑

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆