Delete Rows by Cell Value - Delete sheets w/out prompt

Hi, I have a macro that formats data exported from a database that I can then copy and paste into another spreadsheet which pulls the raw data into a pivot table.

The problem:
I keep having to edit the rows in the Macro to delete as the data is different from week to week. I either need to be able to prompt for the row to edit, or preferrably, have an conditional statement that looks for a text value in column A (like "DUDE") and deletes all rows, incuding that one, and below....THEN same code again but this time looking for a different value in column A (like"SWEET") and deleting all rows, including that one, up through row 11.

So lets say the data was 5000 rows with a header column on row 10. I want to delete all rows DUDE - 5000 and then all rows 11 - SWEET, but I would like the macro to look for those words to base the end/start points of the row deletes.

Here is the code I have so far. There is some other formatting in there as well, like deleting extra sheets. ALSO: is there a way to stop excel from prompting on asking me if I was to delete a sheet when it is in the Macro to do so?

Please Login or Register  to view this content.

Something like this, notice all the "selecting" that's been removed?:

Please Login or Register  to view this content.

I am getting an RunTime Error '91' on the RwStart line stating the Object Variable or With block variable not set. I have not changed your code except to update the value of the cell to the actual value I am looking for. I did notice that the value will be in Column D not A, but it looks like your code is watching for the value in xlWhole spreadsheet then grabbing that row number so I don't think it matters where the DUDE or SWEET values are at, correct?

Please Login or Register  to view this content.

xlWhole refers to the value in the cell. xlWhole means the value searched has to be found and be equal to the whole cell value. xlPart would be mean it can match to "part" of any string in any cell.

The part that says Cells.Find is the part that makes it search the whole spreadsheet.

You'll need to post a sample workbook so I can compare what the code is designed to do against what your sheet actually contains.