Find keyword and delete the rows below and above

my request was changed as in 7th post
first part:
firts I want to find a specific word and the delete the rows above the row including found keyword.
In the sample find "there is no guarentee" then delete the rows (row numbers:4-3-2-1) (there is "there is no guarentee" 2 TIMES BUT THE FIRST ONE)

second part

I want to find a specific word and the delete the rows BELOW the row including found keyword.
In the sample find "ringtone to your cell" then delete the rows (row numbers:35-36-37...)) (there is "ringtone to your cell" 2 TIMES BUT THE LAST ONE)

This macro should work for all sheet in workbook.

before.xlsx
after.xlsx

ther'is only one row with the selected keyword in one sheet ?

I'm finding "ringtone to your cell" 2 times in each sheet, you marked in red only the second

Hi, patel45,

are you playing games with the TO?

ther'is only one row with the selected keyword in one sheet ?

I'm finding "ringtone to your cell" 2 times in each sheet, you marked in red only the second

Can´t find any code or hint for a solution in your answers in this thread so far.

Ciao,
Holger

Hi, zeroist,

I used two codes in my attempt to solve your problem. The first features the items you will search for as well as the number of rows to delete above and/or below the found item and a boolean to decide whether to delete or "paint" the area (we could add another parameter for the colour but I choose yellow here).

Please Login or Register  to view this content.

The second will loop through all worksheets and do accordingly:

Please Login or Register  to view this content.

Ciao,
Holger

Thank you HaHoBe.
I am really sorry.you are right there is " ringtone to your cell " 2 times.ok.let me change the statu.

firts I want to find a specific word(there might be more than one.The answer of which one to choose is (the indicator) the FIrst OR the LAST ONE IN THE ROW)
first part:
firts I want to find a specific word and the delete the rows above the row including found keyword.
In the sample find "there is no guarentee" then delete the rows (row numbers:4-3-2-1) (there is "there is no guarentee" 2 TIMES BUT THE FIRST ONE)

second part

I want to find a specific word and the delete the rows BELOW the row including found keyword.
In the sample find "ringtone to your cell" then delete the rows (row numbers:35-36-37...)) (there is "ringtone to your cell" 2 TIMES BUT THE LAST ONE)

This macro should work for all sheet in workbook.

before.xlsx
after.xlsx

Hi, zeroist,

maybe your description is good for you to understand but I´m having problems understanding why in the After workbook Row 12 still shows the item which should have been deleted according to your requirements as I understand them. Before investing more time I would like you to clarify on that and assure that no further change in requirements will come.

A word an ASAP: everybody here in the forum is helping out for free, so any help available can only come if time is on hand for the preson who wants to help and if the problem is laid down clear to understand. If you really are in need of a solution you should consider to use the Commercial Services here but I´m afraid even there you would need to clarify what I asked above (I can assure you that I won´t take care of your thread over there if you decide to do so).

Ciao,
Holger

Hi, zeroist,

maybe this code will perform as you wish:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

Hi, zeroist,

could you be a bit more specific about what I might have missed than just to state something without saying exactly what? When I tested the code on a copied sheet from your Before-workbook the code did exactly what I understood it should do.

You would need to start with EF953570_3 and maybe adjust the parameters which are passed due to the explanation (I entered them from what I read in your latest posts as well what I saw in the demo).

Ciao,
Holger

Hi, zeroist,

although I separated the two procedures when posting here they should be included into either one or 2 standard modules in the very same workbook for the ease of use (I had my versions of the code in the same modules).

Ciao,
Holger

Hi, zeroist,

maybe the best idea that you have missed up till now: make a copy of one sheet, mark the items to find and not to delete in one color (only the characters, say blue), the ones to delete in a different one (characters, red) and the areas to delete in a third colour (range/cells in yellow) and upload that workbook. Then maybe it will be clear to me what is wrong with what I coded from what I understood from your posts.

Ciao,
Holger

Hi, zeroist,

due to constellation you will have to search for ringtone to your cell first as on the start there are 2 occurrances but afterwards there is only one as the first one will have been deleted by the first occurrance of there is no guarentee.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

Hi, zeroist,

if the keywords aren´t found on the sheet that´s the normal way with teh code as it was supplied. Just change the position of the If-Statement that raisees the error, and the code will run through nicely (well, at least not raising any errors on testing):

The calling procedure will remain unchanged and just get a higher version number

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

Hi, zeroist,

I know you are getting angry with me,but one more help,

I prefer to answer to threads where the original request remains the same throughout as the concept was developed on hte first post (which is reverted withthe last request).

This is the code for the whole module in one code block:

Please Login or Register  to view this content.

Ciao,
Holger