Save Excel rows that contain specific text
 

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

Save Excel rows that contain specific text
 

How about datafilterautofiltercopy elsewhere
--
Don Guillett
SalesAid Software

"azSuch" wrote in message
oups.com...
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

Save Excel rows that contain specific text
 

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"

--
Regards,
Tom Ogilvy




"azSuch" wrote:

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