VBA - Find like*

Posted on 2014-08-07
Last Modified: 2014-08-11
I have an autofilter running in some code, it looks for specific words to delete

The naming conventions are not remaining consistent, so i need something that filters for anything that contains "Bulk Upload"

Can someone amend/suggest some code?


   ActiveSheet.Range(Range("A1"), Range("C" & Rows.Count).End(xlUp)).AutoFilter Field:=3, _
        Criteria1:=Array("PCM ASP Bulk Upload", "PCM EU Bulk Upload", "PCM NA Bulk Upload", "PCM LA Bulk Upload", "PCM ME Bulk Upload"), Operator:=xlFilterValues
   ActiveSheet.Range(Range("C2"), Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).EntireRow.Delete Shift:=xlUp

Open in new window

Question by:Seamus2626
    LVL 27

    Accepted Solution

    Replace Line 2 above with this:
       ActiveSheet.Range(Range("A1"), Range("C" & Rows.Count).End(xlUp)).AutoFilter Field:=3, _
            Criteria1:=Array("*Bulk Upload*"), Operator:=xlFilterValues

    Open in new window

    This has wildcards before and after the words "Bulk Upload" so even "Bulk Uploader" might match.  You example array only contained text before "Bulk Upload" so if that would be the case you may want to remove the second asterisk wildcard.

    LVL 24

    Assisted Solution

    For you code above I would encapsulate it in a With statement and drop the Array:

    With ActiveSheet
       .Range(.Range("A1"), .Range("C" & .Rows.Count).End(xlUp)).AutoFilter Field:=3, _
            Criteria1:="*Bulk Upload*", Operator:=xlFilterValues
       .Range(.Range("C2"), .Range("C" & .Rows.Count).End(xlUp)).SpecialCells(xlVisible).EntireRow.Delete Shift:=xlUp
    End With

    Open in new window

    Note the additional dots before the ranges, the With then effectively adds the ActiveSheet before the dot.
    This would make it a lot easier in future to change the sheet being worked with from the active sheet.
    For example just changing ActiveSheet to Sheets("Sheet1") would just require a single change.
    The array is also no longer required, so why keep it :)

    LVL 27

    Expert Comment

    by:Glenn Ray
    Steve... Yours is the most-efficient change...mine is the easiest. :-)
    LVL 24

    Expert Comment

    Indeed Glen, I think both add value to the question.
    You have answered the question asked in the most clear and straight forward way.
    I have added what I thought would help a tad in the long run.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now