From: andrei on

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/showthread.php?t=201052

http://www.thecodecage.com/forumz

From: JLGWhiz on
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" <andrei.4ajnq8(a)thecodecage.com> wrote in message
news:andrei.4ajnq8(a)thecodecage.com...
>
> 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/showthread.php?t=201052
>
> http://www.thecodecage.com/forumz
>


From: andrei on

It works , thanks !


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

http://www.thecodecage.com/forumz