PDA

View Full Version : VBA to delete rows *partial text*



jsabo
02-24-2016, 04:32 PM
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!

SamT
02-24-2016, 06:05 PM
It depends. If the Value is like "Bob Smith2" or if it is like "Bob Smith2 meets Jan Doe3"

Assuming it is the first case, you can use Find with LookAt:=xlPart to find the cells that contain "BOB-SMITH," "BOB-SMITH2," AND "99 Bob-smiths were killed in today's hunt"

In the first two, you can just replace the Cell Value with "BOB-Smith"

In the case of 99 Bob-smiths. you will need to parse the entire String, use InStr, InStrRev, Left and InStr, InStr and Right to extract the first and third parts then recombine them with "BOB_SMITH."