excel - AutoFilter可以从字典键中获取包含性和非包含性通配符吗？

我一直在寻找一种过滤带有两个以上通配符的Excel电子表格的方法。我之前在StackOverflow上问过是否可以直接在VBA中将两个以上的通配符放入AutoFilter中，而不是在工作表中使用高级筛选器，因为我的宏主要通过PowerShell脚本使用，该脚本通过输入进行传递。这些通配符用于过滤各种电子表格，并保存结果。一个非常有帮助的用户提出了一个答案，给出了一个使用Dictionary键的示例宏，然后我将该宏扩展为接受数组作为输入，然后遍历数组中的所有项目以过滤为通配符。太好了，按预期工作！现在，我想扩展它以传递我想排除的更特定的通配符。例如，我要包括“ A *”和“ B *”，但不包括“ BB *”，因此，例如，“ BA *”仍然存在。下面的宏是否可以通过 BB *通过？hierArray仅包含一个由最多10个字符组成的简单字符串列表（但很少超过3个字符）。Public Function multiHier(hierArray As Variant)Dim v As Long, vVALs As Variant, dVALs As ObjectDim colNum As Long, hierLen As Integer, hier As VariantDim rng As RangeSet dVALs = CreateObject("Scripting.Dictionary")dVALs.comparemode = vbTextComparecolNum = Application.Match("*ierarchy*", Range("A1:Z1"), 0)With Worksheets(1) 'If .AutoFilterMode Then .AutoFilterMode = False With .Cells(1, 1).CurrentRegion vVALs = .Columns(colNum).Cells.Value2 For v = LBound(vVALs, 1) To UBound(vVALs, 1) If Not dVALs.exists(vVALs(v, 1)) Then For Each hier In hierArray hierLen = Len(hier) Select Case UCase(Left(vVALs(v, 1), hierLen)) Case hier dVALs.Add Key:=vVALs(v, 1), item:=vVALs(v, 1) Case Else End Select Next hier End If Next v If CBool(dVALs.Count) Then 'populated the dictionary; now use the keys .AutoFilter Field:=colNum, Criteria1:=dVALs.keys, Operator:=xlFilterValues Set rng = Worksheets(1).AutoFilter.Range multiHier = rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 Else multiHier = 0 End If End WithEnd WithdVALs.RemoveAll: Set dVALs = NothingEnd Function (adsbygoogle = window.adsbygoogle || []).push({}); 最佳答案 我将使用!前缀作为丢弃字符，因为这是一个字符。 Dim h As Long, hstr As String 'put these at the top with the other var declarations For v = LBound(vVALs, 1) To UBound(vVALs, 1) For h = LBound(hierArray) To UBound(hierArray) 'I just prefer to work this way hstr = hierArray(h) & Chr(42) 'stick a * on the end If Left(hstr, 1) = Chr(33) And LCase(vVALs(v, 1)) Like LCase(Mid(hstr, 2)) Then 'starts with a ! and pattern matches the value 'matched a discard pattern. check to see if it was previously added If dVALs.Exists(vVALs(v, 1)) Then _ dVALs.Remove vVALs(v, 1) 'get rid of it Exit For 'discarded. do not keep checking to add ElseIf LCase(vVALs(v, 1)) Like LCase(hstr) Then If NOT dVALs.Exists(vVALs(v, 1)) Then _ dVALs.Add Key:=vVALs(v, 1), Item:=vVALs(v, 1) End If Next h Next v创建hierArray字符串时，可以通过首先放置丢弃模式来节省几个周期。这样，它们将不会被添加，然后被删除。在这方面的任何进一步工作都可能需要切换到完整的正则表达式（regexp）模式匹配方法。关于excel - AutoFilter可以从字典键中获取包含性和非包含性通配符吗？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34614417/ (adsbygoogle = window.adsbygoogle || []).push({});