Multiple Filter Criteria for blanks and numbers using wildcard on same field just doesn't work

Question

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)?

Gefen

You can't use wildcards if the data are actually stored as numbers, which seems to be the case. Are all the numbers 4 digits? – Dimitri

What constitutes "a variety of combinations following"? You can convert the numbers to text if you want look for the Starts With option. Or find numbers 6140<=x<=6149. – Hultgren

Answer 1

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}

Cuthbertson

Great answer, just used with with minimum modifications :) – Charlsiecharlton

Answer 2

Consider add ' in each of your data.

Example :
100 => '100

Plucky