How to filter only special characters and blank using VBA

[MohanBabu1023]

Hi,

I need VBA coding to autofilter only special characters and blank in column.Can anyone help on this.

Regards,
Mohan

 

[Luke M]

Easiest way is probably to record a macro of yourself filtering for those values. Final code will look something like this

ActiveSheet.Range("$E$1:$E$10").AutoFilter Field:=1, Criteria1:=Array("?", _
        "<", ">", "#", ""), Operator:=xlFilterValues

With the array as big as you need it to be. You'll need to define what all you mean by "special characters".

 

[Sahadev Reddy]

Sub AutoFilter()
    Dim AFRg As Range
    Dim R As Range
    Dim Stg As String
    Dim I As Integer
    Application.ScreenUpdating = False
    Set AFRg = Range("A1:A20")
    Stg = "(1234567890abcdefghijklmnopqrstuvwxyz.@)"
    For Each F In AFRg
        For I = 1 To Len(F)
            If (Len(Stg) - Len(Replace(Stg, Mid(F, I, 1), "")) <> 0) Then
                F.Font.ColorIndex = 3
                Exit For
            End If
        Next I
    Next F
    AFRg.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$20").AutoFilter Field:=1, Criteria1:=RGB(255, 0 _
        , 0), Operator:=xlFilterFontColor
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Application.ScreenUpdating = True
End Sub

Mod Edit: Code tags added.