Auto Filter Examples

     

    Wildcard Character STAR:

  • Denotes single character

    •  

    Wildcard Character Question Mark:

  • Denotes one or more characters

    •  

    Click on below mentioned image to watch the video:

    Download the Workbook

     

  • Filter the values in the first column which starts with "A"

    • Range("A1").AutoFilter Field:=1, Criteria1:="A*", Operator:=xlFilterValues

     

  • Filter the values in the first column which doesn't starts with "A"

    • Range("A1").AutoFilter Field:=1, Criteria1:="<>A*", Operator:=xlFilterValues

     

  • Filter two letter word which starts with "A" from the first column

    • Range("A1").AutoFilter Field:=1, Criteria1:="A?", Operator:=xlFilterValues

     

  • Filter the data in the first column which starts with either "A" or "P"

    • Sh.Range("A1").AutoFilter Field:=1, Criteria1:="A*", Operator:=xlOr, Criteria2:="P*", Operator:=xlFilterValues

     

  • Filter the data in the first column which starts with "A" and ends with "e"

    • Sh.Range("A1").AutoFilter Field:=1, Criteria1:="A*e", Operator:=xlFilterValues

     

  • Filter the data in the first column where "P" exists in second position of a string

    • Sh.Range("A1").AutoFilter Field:=1, Criteria1:="?p*", Operator:=xlFilterValues

     

    Sub FilterTheDataWithTheHelpOfWildCardCharacters()

    'Define the worksheet
    Dim Sh As Worksheet
    Set Sh = ActiveWorkbook.Sheets("Sheet2")

    'Filter the Data in the first column which starts with "A"
    Sh.Range("A1").AutoFilter Field:=1, Criteria1:="A*", Operator:=xlFilterValues
    Sh.AutoFilterMode = False

    'Filter the Data in the first column which doesn't starts with "A"
    Sh.Range("A1").AutoFilter Field:=1, Criteria1:="<>A*", Operator:=xlFilterValues
    Sh.AutoFilterMode = False

    'Filter three letter word which starts with "P" from the first column
    Sh.Range("A1").AutoFilter Field:=1, Criteria1:="=P??", Operator:=xlFilterValues
    Sh.AutoFilterMode = False

    'Filter the data in the first column which starts with either "A" or "P"
    Sh.Range("A1").AutoFilter Field:=1, Criteria1:="A*", Operator:=xlOr, Criteria2:="P*", Operator:=xlFilterValues
    Sh.AutoFilterMode = False

    'Filter the data in the first column which starts with "A" and ends with "E"
    Sh.Range("A1").AutoFilter Field:=1, Criteria1:="A*e", Operator:=xlFilterValues
    Sh.AutoFilterMode = False

    'Filter the data in the first column where "P" exists in second position of a string
    Sh.Range("A1").AutoFilter Field:=1, Criteria1:="?p*", Operator:=xlFilterValues
    Sh.AutoFilterMode = False

    End Sub

     

     

Hi Welcome to VBA Tutorial.