Macro to KEEP only the rows containing certain text, delete the rest.

[linda88]

Im looking for a macro/code to KEEP only the rows containing certain text, and delete the remaining rows. This is a little tricky because for example I want to keep rows containing "xxxx" and to keep rows containing "yyyy", and "zzzz", then delete or clear all remaining rows.
The tricky part is that once the code find the rows containing "xxxx" it cannot immediately delete ALL other rows because some of those rows may contain "yyyy" or "zzzz".

 

[Michael M]

I'm a little confused.
Do you mean if the code finds "xxx" then it cannot follow by deleting "yyy" and "zzz" ??
Can you post a small sample of data using the XL2BB button in the Reply toolbar and show us what you want to happen ?

 

[linda88]

im having trouble with the add-in.
Basically Im saying suppose I have the following data in , say column "d" of a spreadsheet (in rows 1 to 10). Assume random data in the other cells of the spreadsheet.

54677
yjthsacv
yyyy
77678
67756223
xxxx
76456
897809
zzzz
gtrfryhrg

I want only rows 3, 6, and 9 as shown above to remain after running the code. as shown below. Rows 1,2,4,5,7,8, and 10 would be deleted or cleared.

yyyy
xxxx
zzzz

 

[Michael M]

Is "yyy" or "zzz" or "xxx" part of a larger string in the cell or the only text in the cell ?

 

[Michael M]

Use this if the "xxx" or other is the ONLY text in the cell

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
For r = lr To 1 Step -1
    If Range("D" & r).Value <> "zzzz" And Range("D" & r).Value <> "xxxx" And Range("D" & r).Value <> "yyyy" Then
        Rows(r).Delete
    End If
Next r
End Sub

 

[Peter_SSs]

im having trouble with the add-in.

The link for XL2BB in the Reply window (or Michael's or my signature blocks) takes you to detailed installation and use instructions. We want to make XL2BB work for everybody if possible so can you describe at what point of the process things go wrong and exactly what it is that goes wrong?

 

[linda88]

Is "yyy" or "zzz" or "xxx" part of a larger string in the cell or the only text in the cell ?

They would be the only string in the cell, but....if they werent, I would think replacing "yyy" with something like ="<>*yyyy*" in the code would take care of that.

 

[linda88]

The link for XL2BB in the Reply window (or Michael's or my signature blocks) takes you to detailed installation and use instructions. We want to make XL2BB work for everybody if possible so can you describe at what point of the process things go wrong and exactly what it is that goes wrong?

Im using excel 2002 and it didnt recognize the file or filetype as a valid add-in

 

[linda88]

I meant excel 2003

 

[linda88]

Use this if the "xxx" or other is the ONLY text in the cell

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "D").End(xlUp).Row
For r = lr To 1 Step -1
    If Range("D" & r).Value <> "zzzz" And Range("D" & r).Value <> "xxxx" And Range("D" & r).Value <> "yyyy" Then
        Rows(r).Delete
    End If
Next r
End Sub

I tried this but it actually deletes everything. All the rows.