VBA to delete rows *partial text*

[jsabo]

I have seen a lot of VBA out there for searching a column and deleting rows which are empty, or deleting ones where criteria is not met. I have a slightly different request.

I would like to do much the same, but have the VBA look at a partial string. I.e, I would like for it to look for "BOB-SMITH" and keep those rows where the criteria is met. However, this database lacks any sort of control, so people may put "BOB-SMITH2". In the event that the script finds "BOB-SMITH2" where the criteria is "BOB-SMITH", then it will trim down "BOB-SMITH2" to match the criteria. I would also prefer that it trims from both directions, if need be. Then, as most instances of a script like this, I would like it to delete rows where the criteria doesn't match/the field is null. Is something like this possible?

Thanks in advance!

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[jsabo]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I have attached a workbook. There's a before and after tab. Let me know if you require clarification and thanks for your time.

EDIT: Would be ideal for it to trim from both directions in case of "bbBOB-SMITHhh"

[CAntosh]

Here's my effort. It's designed for the workbook you posted, but I've indicated where to make changes if you need to switch names/columns.

Please Login or Register  to view this content.

[xladept]

I used an input box for the criterion - we can change that:

Please Login or Register  to view this content.