+ Reply to Thread
Results 1 to 6 of 6

Delete Method of Range Class Failed on SpecialCells(xlCellTypeBlanks).EntireRow.Delete

  1. 04-30-2021, 11:43 AM #1
    BFid
    BFid is offline
    Registered User
    Join Date
    10-14-2009
    Location
    NC
    MS-Off Ver
    Office 365
    Posts
    11

    Delete Method of Range Class Failed on SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    I have a workbook with many spreadsheets with tables. I am trying to write code to remove all the rows for the selected table where the cell value is blank in the "QTY" column.

    First I select a cell in the table then run the below code.

    I get Run time error '1004' - Delete Method of Range Class Failed on the line that is to delete the rows.

    I have verified that the worksheet is not protected.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 04-30-2021, 12:18 PM #2
    Marc L
    Marc L is offline
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Delete Method of Range Class Failed on SpecialCells(xlCellTypeBlanks).EntireRow.Delete


    SpecialCells is a mess when nothin' matches as it raises an error. So the better is to try a filter for example …
    Reply With QuoteRegister To Reply
  3. 04-30-2021, 12:31 PM #3
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: Delete Method of Range Class Failed on SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    Another possiblility is that your empty cells are not contignous range. In case of table it could lead to problems. If that is the case (not the point mentioned by Marc L - but please explore also that), such modification would probably help:

    Please Login or Register  to view this content.
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  4. 05-01-2021, 04:22 AM #4
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,239

    Re: Delete Method of Range Class Failed on SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    Guys...Unless I am missing something???


    Please Login or Register  to view this content.
    When referencing a table the use of "EntireRow" will cause this error...

    Try...Comment out the error coding if you are certain that some tables will have no blank cells in Qty Column
    And if you have many sheets, why not incorporate a sheet loop
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 05-01-2021 at 04:28 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  5. 05-05-2021, 04:00 PM #5
    BFid
    BFid is offline
    Registered User
    Join Date
    10-14-2009
    Location
    NC
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Delete Method of Range Class Failed on SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    This worked perfectly for me! Thank you for your help!!

    Quote Originally Posted by Kaper View Post
    Another possiblility is that your empty cells are not contignous range. In case of table it could lead to problems. If that is the case (not the point mentioned by Marc L - but please explore also that), such modification would probably help:

    Please Login or Register  to view this content.
    Last edited by BFid; 05-05-2021 at 04:03 PM.
    Reply With QuoteRegister To Reply
  6. 05-06-2021, 11:27 AM #6
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: Delete Method of Range Class Failed on SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    Glad it worked for you, and thanks for reputation point.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] error method delete of class range has failed with Select Case
    By MaartenRo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2021, 03:28 AM
  2. Runtime error '1004': delete method of Range class failed
    By adityamishra in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-06-2017, 10:03 AM
  3. Delete Method of Range Class failed
    By kielle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-25-2016, 03:54 AM
  4. Alternative to SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    By twckfa16 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2014, 11:08 AM
  5. Delete method of Range class failed (again)
    By Kelden in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-23-2012, 01:01 AM
  6. Delete method of Range class failed
    By dan_coder in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2006, 05:20 AM
  7. [SOLVED] "delete method of range class failed
    By Kathy Goldie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2005, 02:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1