VBA to delete rows *partial text*

jsabo

New Member
Joined
May 27, 2014
Messages
17
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!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,683
Office Version
  1. 2013
Platform
  1. Windows
What field are we going to look in?
And you want all rows that have "Bob Smith" Deleted is that correct?
 

jsabo

New Member
Joined
May 27, 2014
Messages
17
What field are we going to look in?
And you want all rows that have "Bob Smith" Deleted is that correct?

I want it to look through all of column D. Let's say the criteria is "BOB-SMITH". The script might encounter "BOB-SMITH-PITTSBURGH". In this case, I want it to trim from the right to match "BOB-SMITH". The script might encounter "PITTSBURGH-BOB-SMITH". In this case, I would like it to trim from the left to match. Keep any rows which contain "BOB-SMITH". Delete any rows which don't match the criteria or are blanks.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,683
Office Version
  1. 2013
Platform
  1. Windows
Try this:

Code:
Sub Bob_Smith()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
    For i = Lastrow To 1 Step -1
        If InStr(Cells(i, 4).Value, "BOB-SMITH") Then

        Else
            Rows(i).Delete
        End If
    Next
Application.ScreenUpdating = True
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,706
Messages
5,833,238
Members
430,199
Latest member
Petty queen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top