VBA - Find like*

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

Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCommented:
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.
0
Glenn RayExcel VBA DeveloperCommented:
Steve... Yours is the most-efficient change...mine is the easiest. :-)
0
SteveCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.