+ Reply to Thread
Results 1 to 20 of 20

Macro Error When Deleting Columns and Rows

  1. #1
    Registered User
    Join Date
    05-10-2018
    Location
    Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    43

    Macro Error When Deleting Columns and Rows

    Hello!

    So this is a new thing that has cropped up. I have a macro that is supposed to delete columns and rows but it throws the Error "Delete method of Range class failed". I investigated and the macro is the same that I had used with the previous version of Excel, but the document will not allow me to delete columns or rows at all, even manually. I have it unprotected and I can select the columns or rows and right click to delete and it clears the cells of all text but leaves the columns there only blank instead of deleted.
    Example Not Deleting.jpg

    We did just update from Office 2013 to Office 365 and the macro worked before we updated.

    Any help would be appreciated.
    Last edited by Allienne; 09-18-2018 at 04:05 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro Error When Deleting Columns and Rows

    Could you post the code rather than a picture of it?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-10-2018
    Location
    Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Macro Error When Deleting Columns and Rows

    Hello!

    The whole code is:
    Please Login or Register  to view this content.
    Last edited by Allienne; 09-18-2018 at 10:00 AM.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro Error When Deleting Columns and Rows

    Does this work?
    Please Login or Register  to view this content.
    PS When posting code can you please use CODE tags?

  5. #5
    Registered User
    Join Date
    05-10-2018
    Location
    Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Macro Error When Deleting Columns and Rows

    Hello!

    Unfortunately, I get the same error. Also when I try to manually delete the columns, it will clear the cells in the column but will not remove the column.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Error When Deleting Columns and Rows

    See if it runs like this:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  7. #7
    Registered User
    Join Date
    05-10-2018
    Location
    Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Macro Error When Deleting Columns and Rows

    xladept,

    I get a Run-time error '1004': Delete method of Range class failed. This happens at the DC.EntireColumn.Delete section.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Error When Deleting Columns and Rows

    So, it doesn't work in 365 Can you delete them manually?

  9. #9
    Registered User
    Join Date
    05-10-2018
    Location
    Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Macro Error When Deleting Columns and Rows

    xladept,

    No, I think that is part of the problem. I have multiple workbooks that will not allow me to delete the rows and columns. All that happens when I try to delete is the cells in that row or column clear all text. So I have a blank row or column where the deleted columns should have been removed.

    The odd thing is it was working properly last week with Office 365 but no longer. Maybe an update changed the properties?

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Error When Deleting Columns and Rows

    Can you repair your 365??

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Macro Error When Deleting Columns and Rows

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED, scroll down and click Manage Attachments.

  12. #12
    Registered User
    Join Date
    05-10-2018
    Location
    Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Macro Error When Deleting Columns and Rows

    xladept,

    I tried to repair it but it didn't work. It seems to be a specific document that is the issue. I'll have to investigate further.

    Norie,

    It's a specific document that is proprietary so I am unable to upload an example and if I try to recreate one it works fine.

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Error When Deleting Columns and Rows

    I have multiple workbooks that will not allow me to delete the rows and columns.
    What's the story

  14. #14
    Registered User
    Join Date
    05-10-2018
    Location
    Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Macro Error When Deleting Columns and Rows

    xladept,

    It's really odd. It's only this one main document but when I highlight the columns to delete them after I right click delete it clears all the cells but leaves the columns intact. This is not what I need. I tried to create a sample document but it would work fine and the proprietary one still doesn't work. It completely baffles me.

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Error When Deleting Columns and Rows

    But - it works on a copy? That may be your workaround

  16. #16
    Registered User
    Join Date
    05-10-2018
    Location
    Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Macro Error When Deleting Columns and Rows

    xladept,

    The macro works on a separate document, the actual macro doesn't work on the particular workbook that I have to use. I may have to figure something else out regarding it.

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Error When Deleting Columns and Rows

    Does it work on a copy of that proprietary book?

  18. #18
    Registered User
    Join Date
    05-10-2018
    Location
    Pennsylvania
    MS-Off Ver
    Office 365
    Posts
    43

    Re: Macro Error When Deleting Columns and Rows

    Yes, the original copy. I was able to get it to work by recreating the entire workbook. It took a while but it is working.

  19. #19
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Error When Deleting Columns and Rows

    Hallelujah

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Error When Deleting Columns and Rows

    Thanks for the rep!

+ 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. getting a ref error when deleting columns
    By greycamaro in forum Excel General
    Replies: 4
    Last Post: 05-05-2017, 01:29 AM
  2. [SOLVED] Error Checking Macro to cycle through rows looking at specific columns for things
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2015, 08:36 PM
  3. [SOLVED] 2010 Macro Issue - deleting columns and rows
    By Scalpel4 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-03-2015, 07:49 PM
  4. Code/Macro for deleting empty columns and rows.
    By gautamacharya in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-09-2014, 01:49 PM
  5. [SOLVED] Error 13 - Type Mismatch for my macro code for deleting rows based on condition.
    By Whitenoise1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-19-2012, 07:56 PM
  6. VBA 1004 Error Deleting Rows or Columns
    By CaptMac in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-23-2010, 03:46 PM
  7. Error when deleting rows
    By wmureports in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2006, 03:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1