Macro that delets rows if keyword is found

A

andrei

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
 
J

JLGWhiz

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro which delets cell if the cell above has the same keyword 2
Macro - reads cells in a column .If keyword found moves cell conte 5
Macro puts text if keyword is found in cell above 3
Need macro that concatenates cells with text 7
Macro which delets in a cell all characters after comma 3
Need macro that analyses 2 columns . Delets row if same text is fo 3
How to delete ALL cells containing the same data 1
Macro that splits content from cell if given character is found 4

Top