使用通配符自动过滤超过2个条件 [英] Auto filter with more than 2 criteria with wildcards

问题描述

我在宏中使用以下代码:

I'm using the following code in a Macro:

Sub Macro2()
    Sheets("Bill").Select
    Range("A2:AA2").Select
    Rows("2:2").Select
    Selection.AutoFilter
    Range("T2").Select
    ActiveSheet.Range("$A$2:$AA$111").AutoFilter Field:=20, Criteria1:=Array("=*Base ch*", _
    "=*Service*", "=*Supply Ch*", "=*Customer*", "=*Analyst*"), Operator:=xlFilterValues
End Sub

它没有显示任何错误，当我仅使用2个条件时，它可以完美运行.问题是当我尝试2次以上时.它什么也没显示.

It's not showing any error and when I use only 2 criteria It works perfect. The issue is when I try more than 2. It doesn't show anything.

我尝试了操作符 OR ，但我认为这仅适用于2个条件.

I tried the operator OR but I think that only works with 2 criteria.

有什么建议吗?

推荐答案

在处理多个通配符标准时，请尝试使用 Advanced Filter 代替 AutoFilter .您所需要做的就是要么在工作表中列出那些条件以及通配符，要么以编程方式创建条件表并将其删除.

Instead of AutoFilter, try Advanced Filter when dealing with multiple wildcard criteria. All you need is to either have a sheet with those criteria listed along with wildcard or create the criteria sheet programmatically and delete it in the end.

下面的代码将以编程方式创建一个条件表，并在条件表中从行2和行A1开始的A列中列出所有条件，其中将包含数据表中的列标题.

The below code will create a criteria sheet programmatically and list all your criteria in column A starting from Row2 and A1 on criteria sheet will contain the column header from your data sheet.

Sub FilterWithMultipleWildcardCriteria()
Dim ws As Worksheet, wsCriteria As Worksheet
Dim strCriterai
Dim lr As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set ws = Sheets("Bill")
If ws.FilterMode Then ws.ShowAllData
lr = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

On Error Resume Next
Set wsCriteria = Sheets("Criteria")
wsCriteria.Cells.Clear
On Error GoTo 0

If wsCriteria Is Nothing Then
    Set wsCriteria = Sheets.Add
    wsCriteria.Name = "Criteria"
End If

wsCriteria.Range("A1").Value = ws.Range("T2").Value
strcriteria = Array("*Base ch*", "*Service*", "*Supply Ch*", "*Customer*", "*Analyst*")

wsCriteria.Range("A2").Resize(UBound(strcriteria) + 1).Value = Application.Transpose(strcriteria)

ws.Range("A2:AA" & lr).AdvancedFilter xlFilterInPlace, wsCriteria.Range("A1").CurrentRegion

wsCriteria.Delete

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

这篇关于使用通配符自动过滤超过2个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！