+ Reply to Thread
Results 1 to 10 of 10

deleting rows from multiple sheets in excel 2010

  1. #1
    Registered User
    Join Date
    10-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    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

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,081

    Re: deleting rows from multiple sheets in excel 2010

    Hi Steve. Try this macro on a copy of your workbook:
    Please Login or Register  to view this content.
    I just used column E as the last used column in your sheets. Change the "E" in the code to match your last used column.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    10-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: deleting rows from multiple sheets in excel 2010

    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
    Attached Files Attached Files

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,081

    Re: deleting rows from multiple sheets in excel 2010

    I didn't know you had tables set up. Try:
    Please Login or Register  to view this content.

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,081

    Re: deleting rows from multiple sheets in excel 2010

    I forgot to mention that some of your products data in column A have trailing spaces in them. This mean that the macro I suggested will not find those products. Run the following macro first to remove any trailing spaces in the data in column A. You only have to run this macro once. Then try the macro I posted in Post#4.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    10-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: deleting rows from multiple sheets in excel 2010

    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

  7. #7
    Registered User
    Join Date
    10-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: deleting rows from multiple sheets in excel 2010

    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

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,081

    Re: deleting rows from multiple sheets in excel 2010

    Hi Steve. Click on this link to download your file: https://app.box.com/s/7u5x0br7rt16t8beft8alq2ac37nvnm1 Run the macro in Module2. It works properly when I try it. Module3 contains the 'RemoveSpaces' macro.

  9. #9
    Registered User
    Join Date
    10-11-2011
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: deleting rows from multiple sheets in excel 2010

    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
    Attached Files Attached Files

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,081

    Re: deleting rows from multiple sheets in excel 2010

    Hi Steve. Click this link to download your file. https://app.box.com/s/t2y6raa1jm771xu36iquglreettme39r There should be no need to remove the "Remove Spaces" macro. I've made one minor change to the "DelRows" macro to make it slightly more efficient but everything worked properly when I tested it, even before the minor change. Give it a try.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 06-07-2018, 04:16 PM
  2. Replies: 2
    Last Post: 03-16-2016, 03:16 PM
  3. Deleting ROWS via VBA...Excel 2010
    By rlc2120 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-19-2013, 08:12 PM
  4. [SOLVED] Excel 2010: Referencing same rows & columns from multiple sheets
    By cat2005 in forum Excel General
    Replies: 14
    Last Post: 11-14-2013, 12:19 PM
  5. [SOLVED] Deleting rows across multiple sheets?
    By kickme93 in forum Excel General
    Replies: 1
    Last Post: 09-16-2013, 09:08 AM
  6. [SOLVED] Deleting matching rows from different excel sheets
    By cnash52 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-06-2013, 03:10 PM
  7. Deleting Rows Excel 2010
    By extrapulp in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-27-2010, 08:51 AM
  8. [SOLVED] Updating, Deleting and inserting rows over two Excel Sheets
    By Dilip Mistry in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1