Macro that delets rows if keyword is found
in [Programming]
|
From: andrei on 6 May 2010 10:22 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 6 May 2010 11:48 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 10 May 2010 09:30 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
|
Pages: 1 Prev: Display Math Symbols in UserForm Next: Any way to show / hide sheets |