excel autofilter where criteria is the cell value must not contain a letter or a number

Question

i have a range A to N, initially i filter it by H(field 8) then I want to filter it again by column N(field 14) with the criteria that the rows in column N must not contain a letter or a number, how would be the way to do that?

rng.AutoFilter Field:=8, Criteria1:="Value" 'Filter by Column H
rng.AutoFilter Field:=14, Criteria1:=       'Filter by Column N

Think about introducing a helper `TRUE/FALSE` column populated by an Excel formula possibly by using `FilterXML`. JvdV is an authority on it so check out [his legendary post](https://stackoverflow.com/q/61837696). If you would populate it with VBA, the code could look something like this: `Data = rng.Columns("N").Value: For r = 1 To UBound(Data, 1): If CStr(Data(r, 1)) Like "[A-Za-z0-9]" Then Data(r, 1) = True Else Data(r, 1) = False: Next r: rng.Columns(NewColumn).Value = Data`. — VBasic2008, Mar 09 '23 at 10:37

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ · Accepted Answer · 2023-03-10T08:14:23.870

Have to create another column with this formula and filter this column:

Function hasDigitsOrNumbers(s As String) As String
    Dim ch As String, ln As Long
    Dim i As Long
    
    ln = Len(s)

    For i = 1 To ln
        ch = Mid(s, i, 1)
        If (ch >= "0" And ch <= "9") Or (ch >= "a" And ch <= "z") Or (ch >= "A" And ch <= "Z") Then
            hasDigitsOrNumbers = True
            Exit Function
        End If
    Next
    hasDigitsOrNumbers = False
End Function

=hasDigitsOrNumbers(N1) and returns true if the N1 has digits or letters, otherwise returns FALSE.

Reading VBasic2008's comment I wrote a new function which is about 2.5 times faster:

Function hasDigitsOrNumbers(s As String) As String
    hasDigitsOrNumbers = (s Like "*#*") Or (s Like "*[a-z]*") Or (s Like "*[A-Z]*")
End Function

excel autofilter where criteria is the cell value must not contain a letter or a number

1 Answers1