VBA: I want to delete a range of row within specific text ?

[maria.blue44]

I want to delete a range of rows [from an input text box up-to another input text box with specific text] in all sheet of an excel book.


Say for example: a cell contains "first data text" and in some other cell below in a row - a cell value starts with "finished data range". I want to delete each row in between including the row contains "first data text" and "finished data range" row.

In the 2nd scenario, I want to keep the "finished data range" row as well.

And in scenario 3, I want to delete up-to the row below "finished data range".

And in scenario 4 I want to delete up-to 2 row below "finished data range".

Please help. I am still learning.

[arlu1201]

So you need 4 different codes?

Do you have a sample file that you can upload?

[maria.blue44]

Yes you got it. Please find the excel sheet. Range is colored in yellow in the atachment. The code should not work in SheeA, sheetB and SheetC. Thanks in advance.

[WasWodge]

Try the attached. To run click the button in Sheet1.
Please note that the code doesn't select cells that start with...

a cell value starts with "finished data range"

It works on any cell that contains the text i.e. if you type "first" in the top box and "last" (both without the quotation marks) in the bottom box it will delete from row 2 to 8 using scenario 1.

The code should not work in SheetA, sheetB and SheetC

I have assumed by this that you do want it to work on the other 3 sheets at the same time.

[maria.blue44]

This is not working
I want a macro without a button or form.

[maria.blue44]

There can be any number of sheets instead of "Sheet1", "Sheet2", "Sheet3".

[maria.blue44]

Again tried it worked BUT I want a macro without a button or form.
There can be any number of sheets instead of "Sheet1", "Sheet2", "Sheet3"......
Sheet A -C will be unaffected.

[WasWodge]

Please Login or Register  to view this content.

Then explain exactly how you want to enter the text you want the search/find to act on (please note in your sample spreadsheet no sheet contains the text "finished data range") ,what trigger you want to use to start the macro's and how you are looking to run the 4 macro's sparately

[maria.blue44]

I will call all the 4 macros with a single macro. In each macro I want to enter texts with comas (,). The text can be anything.

[maria.blue44]

VBA code will have two lines for : range from and range to.
In range from and range to I want to enter my texts with commas.
Thanks in advance.

[WasWodge]

2 things I don't understand.

1) What are the commas there to do? You normally use comma's to separate information that is being used for the same task like you do in an array.
If you are are typing in 2 lines of text then you would normally just assign them to 2 variables and then use them in the find (or obviously you could type them directly in the find)
i.e. something like

Please Login or Register  to view this content.

Like I said I don't see where the comma's come into play in the code.

2) You say you want the code all in a single macro but you still don't say how you want the code to decide which scenario to run. Code isn't psychic, it needs to be told if this happens then do that or it needs to have user input to tell it what to do i.e. an input box, userform etc.

[maria.blue44]

1. I have multiple pages and multiple text range to delete with those four conditions. So I thought of commas to mention the multiple text range with a similar condition.
2. Ahhh!! yup then I have to run them separately. I apologize for my mistake.

[WasWodge]

You don't have to run them separately necessarily, you can have them in the same code. For instance you could aIllocate a cell and then type in a number from 1 to 4 to choose the option or get an inputbox to appear.
Personally I would prefer the inputbox myself.

[maria.blue44]

Thank you for your quick response. I want a little change in the code. There can be any number of sheets instead of "Sheet1", "Sheet2", "Sheet3" only. And the code will not work on sheet A-C. Thanks in advance.

[WasWodge]

I was already going to do that but I was waiting for an answer to the previous questions first. What I will do Is put the code altogether in one macro similar to how I would do it and then we will take it from there.
It won't be for a couple of hours when I get home from work.

[WasWodge]

Try the attached. When the input box comes up put in the scenario number (only the number and the scenario's go in the same order as in your first post).
I have put a copy of the code below and when you want to change the text you change the text in red in the code.

I must admit though if it was me I'd still use a userform to enter the text and scenario number in one go from the spreadsheet but that's just me.

Please Login or Register  to view this content.

[maria.blue44]

Worked. Thank you once again.

[maria.blue44]

How will I identify copy a range by text to another range say from D1:F8 to A1: C8 ?

[WasWodge]

Different question really and so if you need more help then you will need to start a new thread and I am sure someone will give some guidance but basic principle would be something like...

Please Login or Register  to view this content.

[maria.blue44]

One related question: what if first and last appears in sentences. How will it work then ?
And in any column ?

[WasWodge]

XlPart means it should pick up words within sentences. If you don't want this to happen change it to XlWhole.
It isn't Column specific.

[maria.blue44]

Some times same range is occurring multiple times in sheets which I want to delete. How I can tackle that ?

[sroysroy]

Another Related question: why the code does not work on web fteched data with ".WebFormatting = xlWebFormattingAll" ?

[WasWodge]

Hi Marie, I am afraid I need a bit more info about your layout before I can propose an option. Can you let me know the following

1) are you saying the range is the same as the active sheet i.e. you want exactly the same range deleted literally on every sheet or do you mean the same wording i.e. "first" and "last" appears in each sheet and you want to delete those ranges on each sheet?

2) by sheets do you mean every sheet ion the workbook or certain named sheets?

or

3) or multiple ranges on the same sheet (or a combination of this and all/any of the above scenario's)?

sroysroys, I am afraid web scraping etc. is my major weakness as I rarely do any but (out of personal interest really as well) can you try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and run it on a copy of your data just to see what happens?

[maria.blue44]

By sheet I mean one single sheet named master sheet in the workbook.
As I collected data from all sheets so the range within name "first" and "last" appears multiple times that I want to delete.
I am not able to loop it.