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!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,258
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
17,258
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,969
Members
414,353
Latest member
ljhan

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