Results 1 to 4 of 4

Thread: [VBA] Using Ucase & Like With Filters
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Posts
    196
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default [VBA] Using Ucase & Like With Filters

    Hello all,
    I'm trying to create a filter so that i can delete anything that contains "Atv", "Utv", "Duallie", and "Dually"
    Issues arise when those phrases are sometimes capitalized, sometimes capslocked, and sometimes lowercase.
    This is my first attempt with filters in VBA which does the job, but if theres a more efficient way of doing this i'm all ears.
    I've been reading up and i like the sound of using Like and Ucase, but i'm unsure of how to incorporate it with what i'm trying to accomplish

    Code:
    Sub obscureFILTER()
        Dim ws As Worksheet
        Dim rng As Range
        Dim lastRow As Long
    
        Set ws = ActiveSheet
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
        Set rng = ws.Range("B1:B" & lastRow)
    
        With rng
            .AutoFilter Field:=1, Criteria1:="=*Atv*", Criteria2:="=*Utv*", Operator:=xlOr
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
    
        ws.AutoFilterMode = False
        
        With rng
            .AutoFilter Field:=1, Criteria1:="=*ATV*", Criteria2:="=*UTV*", Operator:=xlOr
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
    
        ws.AutoFilterMode = False
    
        With rng
            .AutoFilter Field:=1, Criteria1:="=*atv*", Criteria2:="=*utv*", Operator:=xlOr
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
    
        With rng
            .AutoFilter Field:=1, Criteria1:="=*Duallie*", Criteria2:="=*Dually*", Operator:=xlOr
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
    
        ws.AutoFilterMode = False
        
        With rng
            .AutoFilter Field:=1, Criteria1:="=*DUALLIE*", Criteria2:="=*DUALLY*", Operator:=xlOr
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
    
        ws.AutoFilterMode = False
    
        With rng
            .AutoFilter Field:=1, Criteria1:="=*duallie*", Criteria2:="=*dually*", Operator:=xlOr
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
    
        ws.AutoFilterMode = False
    
    
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,078
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: [VBA] Using Ucase & Like With Filters

    Autofilter is not case sensitive, so it will find ATV, atv, atV etc
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Posts
    196
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VBA] Using Ucase & Like With Filters

    Quote Originally Posted by Fluff View Post
    Autofilter is not case sensitive, so it will find ATV, atv, atV etc
    awesome, was unaware. thank you

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,078
    Post Thanks / Like
    Mentioned
    390 Post(s)
    Tagged
    41 Thread(s)

    Default Re: [VBA] Using Ucase & Like With Filters

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •