You could do an Advanced Filter "Data -> Advanced" like this:
The titles of the <<criteria Range>> and <<Filtered Range>> MUST match the title of the list ("PARTNUMBER") in this case. This will extract the rows you want to keep into cell G2 according to the wildcard matches (*-D*, etc) in the criteria range column. See the screenshot below. There are even more powerful methods if you get into Excel database functions (DGET, DCOUNT, etc).
Thanks for the suggestion. I have additional 20-30 columns that would need to be deleted as well. I'm trying to delete the rows based on the values in column A, using the "contains" criteria I mentioned above. I have used the method you mentioned in your response, and it worked well when I had used it. I could have used autofilter API, but the criteria options is limited.
What about this is your hangup? It's pretty darn simple.
Just run the following through a pseudocode-to-VBA converter (your brain).
Add all your "safe words" to an array.
while cell isn't blank
bFound = false
for each safe word in array
if cell contains safe word
bFound = true
next safe word
if not bFound then
cells(1,current row).entirerow.delete xlup
'Don't increment row
I've done the word filter before, I think I'm more hung up on the "containing" with possibly then need for wildcards. I'm not sure.
As always I appreciate all input and ideas.
I don't understand. If you put your entire "database", all the rows and columns in the "List range" field of the advanced filter and choose "Filter the list in place" you'll hide all the rows you do not want. From there you can cut and paste the remainder to another place. Actually, I think Excel might have a delete hidden rows option which might work on the list in-place.
This is a excel file that has to follow strict formatting, all I am allowed to do is delete rows. I'm not allowed to add rows, columns, or any other additional data to the file.
Extremely crude code of possibly answering your question
Dim flagFound As Boolean
Dim yRow As Long
Dim PartNumber As String
flagFound = False
Application.ScreenUpdating = False
For yRow = 24 To 1 Step -1
PartNumber = Range("A" & yRow).Text
flagFound = InStr(1, PartNumber, "-D", vbTextCompare)
flagFound = flagFound + InStr(1, PartNumber, "-S", vbTextCompare)
flagFound = flagFound + InStr(1, PartNumber, "-M", vbTextCompare)
flagFound = flagFound + InStr(1, PartNumber, "RVS-", vbTextCompare)
flagFound = flagFound + InStr(1, PartNumber, "RVD-", vbTextCompare)
If flagFound = False Then
Rows(yRow & ":" & yRow).Select
flagFound = False
Application.ScreenUpdating = True
Thanks Solid Air,
You put me in the right direction and in the future will be easily edited/modified when my purchasing department changes their mind again.
As a side note...i had totally forgot about textcompare.
Open up... here comes the airplane... Brmmmmmm Brmmmmm Good boy!
For the constant words of encouragement.