Save Excel rows that contain specific text

[azSuch]

Hi,
I got this code at http://www.mvps.org/dmcritchie/excel/delempty.htm
It deletes the rows that contain the text ANN. What Im asking for is
the opposite. Can it be modified to save the rows that contain the
text ANN???????

Sub Find_ANN()
Dim rng As Range
Dim what As String
what = "ANN"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub

 

[Don Guillett]

How about data>filter>autofilter>copy elsewhere

 

[Tom Ogilvy]

Assume you data is in column A
Sub DeleteNonANN()
dim rng as Range, rng1 as Range
set rng = range(.cells(2,1),.cells(rows.count,1).End(xlup))
set rng1 = rng.offset(-1,0).Resize(rng.rows.count + 1,1)
rng1.AutoFilter Field:=1,
Criteria1:="<>*ANN*"
rng2.specialcells(xlVisible).Entirerow.Delete
activesheet.AutoFilterMode = False
End sub

if the rows to keep should contain only the complete word ANN, then change
Criteria1:="<>*ANN*"
to
Criteria1:="<>ANN"