Find and delete all other rows
 

Hi all
I am trying to get a Macro to find the word or part of the word in a cell.
All cells that do not have that word should be deleted.
If I use the follwoing it does not work

Set c = .Find("test", LookIn:=xlValues)
If c Is Nothing Then
c.EntireRow.Delete
Else
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If

Find and delete all other rows
 

If the range that you're looking through is a single column, you could apply
Data|Filter|autofilter to that column.

Then filter using Custom|Does not contain|test
and delete the visible rows

And then remove the filter.

Murray wrote:

Hi all
I am trying to get a Macro to find the word or part of the word in a cell.
All cells that do not have that word should be deleted.
If I use the follwoing it does not work

Set c = .Find("test", LookIn:=xlValues)
If c Is Nothing Then
c.EntireRow.Delete
Else
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If

--

Dave Peterson