In A column i have many cells with text . Sometimes cells with text ,
sometimes empty cells . From time to time consecutive cells ( like A5
and A6 or A5 , A6 , A7) have the same keyword . I want the macro , in
this case, to delete the rows , keeping only the first one in a series
of consecutive cells . I give an example

Let's say the kwyword is "mother"

A1: mother goes home
A2: empty
A3: mother and father
A4: my son and my mother
A5: My mother is sick
A6: Gather some ....
A7: empty cell

The result should be :

A1: mother goes home
A2: empty
A3: mother and father
A4: Gather some...
A5: empty cell

Many thanks


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=201052

http://www.thecodecage.com/forumz

Give this a shot:

Sub mother()
Dim lr As Long, sh As Worksheet, rng As Range
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
For i = lr To 2 Step -1
If InStr(sh.Cells(i, 1), "mother") 0 And _
InStr(sh.Cells(i-1, 1), "mother") 0 Then
sh.Rows(i).Delete
End If
Next
End Sub


"andrei" wrote in message
...

In A column i have many cells with text . Sometimes cells with text ,
sometimes empty cells . From time to time consecutive cells ( like A5
and A6 or A5 , A6 , A7) have the same keyword . I want the macro , in
this case, to delete the rows , keeping only the first one in a series
of consecutive cells . I give an example

Let's say the kwyword is "mother"

A1: mother goes home
A2: empty
A3: mother and father
A4: my son and my mother
A5: My mother is sick
A6: Gather some ....
A7: empty cell

The result should be :

A1: mother goes home
A2: empty
A3: mother and father
A4: Gather some...
A5: empty cell

Many thanks


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=201052

http://www.thecodecage.com/forumz

It works , thanks !


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=201052

http://www.thecodecage.com/forumz