?
Solved

VBA - Find like*

Posted on 2014-08-07
4
Medium Priority
?
303 Views
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?

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

0
Comment
Question by:Seamus2626
  • 2
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1600 total points
ID: 40246974
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
 
LVL 24

Assisted Solution

by:Steve
Steve earned 400 total points
ID: 40248059
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
 
LVL 27

Expert Comment

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

Expert Comment

by:Steve
ID: 40249344
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

864 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