CODEWITHSUNDEEP
excelfilteringvba
terny
terny

Reputation: 21

vba filter only on character value

I want to make a filter to see rows that contain any character.

For example, column A contains 124124, 4231, R2341, RRR

Then I would like to see R2341, RRR from column A. Is there any way to filter only on character string?

Thanks!

Upvotes: 0

Views: 3368

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Use the Text Filter Contains feature in AutoFilter:

enter image description here

Or with VBA:

Sub Macro1()
    Columns("A:A").AutoFilter
    ActiveSheet.Range("$A$1:$A$5").AutoFilter Field:=1, Criteria1:="=*R*", _
        Operator:=xlAnd
End Sub

EDIT#1:

To test for the presence of ANY letter, use this UDF() in a helper column:

Public Function HasLetter(v As Variant) As Boolean
    Dim i As Long, L As Long
    HasLetter = False
    If Len(v) = 0 Then Exit Function
    L = Len(v)
    For i = 1 To L
        If Mid(v, i, 1) Like "[a-zA-Z]" Then
            HasLetter = True
            Exit Function
        End If
    Next i
End Function

and filter on True.

Upvotes: 1

Related Questions