使用相同字段的通配符的空白和数字的多个过滤器条件不起作用 [英] Multiple Filter Criteria for blanks and numbers using wildcard on same field just doesn't work

问题描述

尽管有关这个话题的问题数量很多，我还没有找到我的问题的解决方案（这可能或可能不是我的错）。

我需要自动过滤范围以返回以614开头的空白和数字。

此列已使用以下格式进行预格式化：

  Range（B：C）。NumberFormat =### 0
  

这是为了删除Excel在某些数字上使用的科学计数格式。

然后我使用以下内容应用过滤器：

 使用ActiveSheet 
 .AutoFilterMode = False'删除任何活动过滤器
 .Range（ A1：O1）AutoFilter字段：= 2，Criteria1：=614 *，运算符：= xlOr，Criteria2：==
结束
  pre> 
 
 
无论我如何应用过滤器，包括使用数组，过滤器只返回空白值。我也使用了Criteria1：== 614 *来获得相同的结果。
 
 
 
我唯一确定的是，数字将以614开始，将有以下各种组合。
 
 
 
数据类型有问题吗？空格是字符串，数字是数字？只有一个博客略微解决了这个问题，似乎表明我在这种情况下不能使用通配符。它是否正确？ 
 
 
 
本质上，澳大利亚手机号码的长度为11个字符，只有前3个是常数。这就是为什么我真的想使用通配符来找到这些记录。我需要从数据集中消除这些加上空格。根据你的回答，我在很大程度上意识到我唯一的选择是把数字转换成字符串，如果我想这样做一个一步的过程。这将影响以后的代码。我假设这不能作为自动过滤器标准的一部分（一厢情愿的想法）？ 
解决方案
无论何时遇到限制，您可以使用 
 应用后过滤器 
 
Despite the number of questions on this topic I haven't been able to find the resolution to my issue (which may or may not be my fault).

I need to autofilter a range to return blanks and numbers beginning with 614.

This column has been preformatted using the following:
Range("B:C").NumberFormat = "###0"
This is to remove the scientific notation format that Excel wants to use on some of the numbers.

I then use the following to apply the filter:
With ActiveSheet
    .AutoFilterMode = False 'remove any active filters
    .Range("A1:O1").AutoFilter Field:=2, Criteria1:="614*", Operator:=xlOr, Criteria2:="="
End With
No matter how I apply the filter, including the use of an array, the filter only returns the blank values.  I have also used Criteria1:="=614*" for the same result.

The only thing I am certain about is that the number is going to start with 614 and will have a variety of combinations following.

Is the problem with the data type?  Blanks are strings and numbers are numbers?  There has been only one blog that has slightly addressed this issue and seems to indicate that I cannot use wildcards in this instance.  Is this correct?  

Essentially the numbers are Australian mobile numbers therefore have a length of 11 characters with only the first 3 being a constant.  This is why I really want to use a wildcard to find these records.  I need to eliminate these plus the blanks from the dataset.  According to your responses and what I was largely beginning to realise my only choice is to convert the numbers to strings if I want to do this as a one step process.  This will affect later code.  I am assuming that this can't be done as part of the autofilter criteria (wishful thinking)?  
解决方案
Anytime you run into a restriction on what you can do with the Range.AutoFilter method, simply build a dictionary of matching criteria using VBA's text, number and/or date manipulation and apply the keys of the dictionary to an AutoFilter operation as an array.
Sub wildcard_Number_Filter()
    Dim a As Long, aTMPs As Variant, dVALs As Object

    Set dVALs = CreateObject("Scripting.Dictionary")
    dVALs.CompareMode = vbTextCompare

    With Worksheets("Sheet1")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            'build a dictionary so the keys can be used as the array filter
            aTMPs = .Columns(2).Cells.Value2
            For a = LBound(aTMPs, 1) + 1 To UBound(aTMPs, 1)
                Select Case True
                    Case Not CBool(Len(aTMPs(a, 1)))
                        dVALs.Item(Chr(61)) = Chr(61)   'blanks
                    Case CStr(aTMPs(a, 1)) Like "614*"
                        'The set of numbers have to be strings in the array
                        If Not dVALs.Exists(aTMPs(a, 1)) Then _
                            dVALs.Add Key:=CStr(aTMPs(a, 1)), Item:=aTMPs(a, 1)
                    Case Else
                        'no match. do nothing
                End Select
            Next a

            'test the array
            'Dim k As Variant
            'For Each k In dVALs.Keys
            '    Debug.Print k & " - " & dVALs.Item(k)
            'Next k

            'filter on column B if dictionary keys exist
            If CBool(dVALs.Count) Then _
                .AutoFilter Field:=2, Criteria1:=dVALs.Keys, _
                                      Operator:=xlFilterValues, VisibleDropDown:=False

            'data is filtered on 614* and blanks (column B)
            'Perform work on filtered data here

        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

    dVALs.RemoveAll: Set dVALs = Nothing
End Sub
By using this method, any manipulation you can perform on values to check them for inclusion or exclusion through standard VBA methods can generate a valid array to be used as a filter set. By bulk loading the values from the worksheet into a variant array, the time to read and evaluate individual cells is virtually non-existent.

        
            Before building and applying filter

        
            After applying filter

                        
这篇关于使用相同字段的通配符的空白和数字的多个过滤器条件不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！