Delete Rows Based on Part of a String Value

[kaneda0149]

I found this fabulous VBA code that delete rows/shift up based on a certain text value. It works great when the cell contains only that specific value but when it's a cell that contains multiple string values and I want to delete based on part of the string in the value.

For example cell text value in column A: "Mickey Mouse Var. Rat"

I want to delete the rows, shift up if any text in column A has in it "Var. Rat". The code below does not work when there are other texts in the cell.

Thanks in advance!

Sub DeleteSpecific_Rows()
' This macro deletes all rows on the active worksheet
' that have the following text in column A.
 Dim rng As Range, cell As Range, del As Range
 Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
 For Each cell In rng
   If (cell.Value) = "Var. Rat" _
    Or (cell.Value) = "VRDN" _
    Or (cell.Value) = "NA" Then
       If del Is Nothing Then
          Set del = cell
       Else: Set del = Union(del, cell)
       End If
    End If
 Next cell
 On Error Resume Next
 del.EntireRow.Delete
End Sub

 

[Diana H]

Please post a sample of your data and expected result.!!

For some reason the original answer provided in this thread did not work the first several tries, but finally worked yesterday. Not sure why, but all is well now and the the code is working perfectly. Thank you!

 

[CerberaJQ]

Apologies for resurrecting this a few years later, however how would one invert this code? Ie keep all rows containing "Var.Rat", but delete all the others?! I have tried swapping the Set del rows, but get a Invalid procedure error on the first Set line...

Any ideas gratefully received!

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG21Jan37
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range, cell [COLOR="Navy"]As[/COLOR] Range, del [COLOR="Navy"]As[/COLOR] Range
 [COLOR="Navy"]Set[/COLOR] rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cell [COLOR="Navy"]In[/COLOR] rng
   [COLOR="Navy"]If[/COLOR] InStr(cell, "Var. Rat") = 0 [COLOR="Navy"]Then[/COLOR]
      [COLOR="Navy"]If[/COLOR] del [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
          [COLOR="Navy"]Set[/COLOR] del = cell
            Else: [COLOR="Navy"]Set[/COLOR] del = Union(del, cell)
          [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] cell
 [COLOR="Navy"]If[/COLOR] Not del [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] del.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[CerberaJQ]

Thanks Mick - that exactly what I needed!

 

[MickG]

You're welcome