macro to select cells containing specific text and delete all cells but these

[JenIT]

I am attempting to sort data containing specific characters and delete
all rows but the ones that meet the critera. Such as search for "LW"
and get all LWLI, LWOS, LWSC, etc.

I have a macro working currently that will select one of the above and
delete all the rest...but I need to make this a bit more robust to keep
all LW's

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) <> "LWLI" 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 '

If I need to go about this from the back side and manually list all vs
a wild card type entry than I will, but I still need some help and
making a multiple selection criteria. When I try this now...It is
seeing the search as exclusive and deleting all data.

 

[Yngve]

hi



Sub NotDelete()


Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each cell In rng

If InStr(cell.Value, "LW") = 0 Then ' delete al rows without "LW"
'If InStr(cell.Value, "LW") <> 2 Then ' ' delete al rows with "LW"

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

Regards Yngve

 

[JenIT]

Hi Yngve:

You are the bomb!!! That works perfect. Thanks for your help!!!!

Jenny

 

[JenIT]

Since you answered this so well, may I pose a follow-up. What if I am
looking for two options only PIT and PAC. But can't use just P as
there are other P's. Is there a way to code for that "or" selection?