VBA - Find like*

Seamus2626
Seamus2626 used Ask the Experts™
on
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?

Thanks


   ActiveSheet.Range("A1:Q1").AutoFilter
   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
   ActiveSheet.Range("A1:Q1").AutoFilter

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel VBA Developer
Top Expert 2014
Commented:
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.

-Glenn
SteveCost Accountant
Top Expert 2012
Commented:
For you code above I would encapsulate it in a With statement and drop the Array:

With ActiveSheet
   .Range("A1:Q1").AutoFilter
   .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
   .Range("A1:Q1").AutoFilter
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 :)

ATB
Steve.
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Steve... Yours is the most-efficient change...mine is the easiest. :-)
SteveCost Accountant
Top Expert 2012

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial