deleting rows from multiple sheets in excel 2010

Hi There

I have a spread sheet where I have created a search criteria to look for a specific value in column "A" across all worksheets within the workbook.

I have this working successfully and when I search for the criteria it returns the required rows from each worksheet.

What I need to do here is to be able to now delete the entire rows from each of the worksheets with one macro (If Possible).

Could anyone help please??

Thanks in advance

Steve

Hi Mumps1

Thanks for your reply.

I did as you requested and I did received a debug notice in the first ws.Range("A1:E" & LastRow).AutoFilter Field:=1, Criteria1:=response

The item was removed from the first worksheet but not from the others (another 2 entries.

How can I get your code to remove all the entries from all of the worksheets all at the same time??

I have attached a copy of the file for your perusal

Regards

Steve

Hi Mumps1

Thanks so much for your prompt reply.

I haven't had a chance to look at the code properly yet as I have to go away until next Thursday on a business trip.

I will look at it as soon as I get back and reply to you.

Thanks again

Have a good weekend

Steve

Hi Mumps1

Following on from my reply last week. I have looked at the code and tested it and I get a debug at the following line of code:

LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

The runtime error is '91'
Object variable or with block variable not set

I also get the same error code in the "Remove spaces" macro

Steve

Hi Mumps1

Sorry for the delay in getting back to you... Hectic is just a short word of the craziness going on here.

I have looked and downloaded your copy of the spreadsheet.

I am still getting an error when using the delete rows option. This is the error I get

ActiveSheet.ListObjects(1).Range.AutoFilter Field:=1, Criteria1:=response

I went through the spreadsheet and formatted it all the same just in case it may have been that throwing the macro out, but alas NO!!

I have attached my updated and formatted copy of the spreadsheet for you as I just removed the "Remove Spaces" macro and it works fine.

I'm not sure if the remove spaces impedes the delete rows macro in any way, but removing it has allowed the deletion macro to work???


If you can come up with any answers, I would be pleased to know.

In the meantime, thanks for all of your help, it is w=very much appreciated and has given me a further insight into writing and using macros

Steve