none
Excel crash when deleting sheet containing Slicers RRS feed

  • Question

  • Am getting worksheet delete failures and Excel "crashes" when trying to delete a couple sheets using VBA.  Experimentation indicated that did not fail if I first (manually) deleted the slicers on the sheet first. Anyone else seeing this?  Is there a code workaround?


    bob mick

    Thursday, February 2, 2017 3:23 PM

Answers

  • Hi,

    We could delete the content of the sheet and then delete the sheet object. In this way, Excel would not crash.

    Sub test()
    Dim s As Worksheet
    Set s = Worksheets("test")
    Dim r As Range
    Set r = s.UsedRange
    r.Delete
    s.Delete
    End Sub

    Regards,

    Celeste

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by bob mick Tuesday, February 14, 2017 1:32 PM
    Monday, February 13, 2017 9:34 AM
    Moderator

All replies

  • Hi,

    If you delete the whole sheets manually instead of deleting slicer firstly, would Excel crash?

    I suggest you share us a sample file for testing.

    If you want to delete slicers using VBA to work around, you could try to use the following code to delete all the slicers in the workbook.

    For Each sliCa In ActiveWorkbook.SlicerCaches
    sliCa.Delete
    Next 

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 3, 2017 1:53 AM
    Moderator
  • Celeste,

    Thanks for your quick response!

    - When I delete the sheet manually, Excel does not crash or hang up.  It operates as normal afterwards.

    - Other sheets in the workbook have slicers and I need to avoid removing all slicers from the workbook.  In fact, a couple of yr ago I decided the slicer objects structure was not clear enough to modify safely (to remove only select slicers).

    - I have a test case (2 small Excel workbooks) that I can share, but do not see a method to attach files?

    bob


    bob mick

    Friday, February 3, 2017 12:53 PM
  • Hi,

    What is your specific version of Office?

    You could upload your file into OneDrive and share the link here.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 6, 2017 1:45 AM
    Moderator
  • Let me know if this link does not work.

    (Using Excel 2016, Win 10PRo on a Dell M4800.)

    bob mick

    https://app.box.com/s/ut1a35pugbmbntsj6xae5camuzqnmjs6

    • Edited by bob mick Thursday, February 9, 2017 2:14 PM
    Wednesday, February 8, 2017 6:08 PM
  • Hi,

    The link doesn't work for users outside your organization, so I failed to download your file.

    Please visit

    Share sites or documents with people outside your organization 

    Share files or folders in Office 365 

    Share OneDrive files and folders

    Besides, make sure I test with the same version, please share the specific version number from File -> Account

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, February 9, 2017 6:19 AM
    Moderator

  • bob mick


    • Edited by bob mick Thursday, February 9, 2017 2:12 PM
    Thursday, February 9, 2017 2:08 PM
  • Hi,

    We could delete the content of the sheet and then delete the sheet object. In this way, Excel would not crash.

    Sub test()
    Dim s As Worksheet
    Set s = Worksheets("test")
    Dim r As Range
    Set r = s.UsedRange
    r.Delete
    s.Delete
    End Sub

    Regards,

    Celeste

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by bob mick Tuesday, February 14, 2017 1:32 PM
    Monday, February 13, 2017 9:34 AM
    Moderator
  • Clever, I don't think I have ever use UsedRange.

    Is there someplace else I should record the crash as a defect?  Or have you already done that.


    bob mick

    Tuesday, February 14, 2017 1:32 PM
  • Hi,

    >>Is there someplace else I should record the crash as a defect?

    Do you want to report the issue?

    In fact I failed to reproduce your issue unless testing your file. I think the issue might cause from improper operation instead of Product issues. The way you create and add the slicers into the current file causes the issue.

    If you want to confirm if it is product issue, due to the limitation of community support channel, I suggest you contact Microsoft Professional Support. If the support engineer determines that the issue is the result of a bug, the service request will be a no-charge case and you won't be charged. Please visit the link below to see the various paid support options that are available to better meet your needs.

    http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Regards,

    Celeste

      




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 15, 2017 8:20 AM
    Moderator
  • Celeste,

    Thank you for your efforts.  However I am very skeptical that the way I created the slicer was the problem.  Adding slicers is very simple, with almost no options.  I did nothing odd when creating mine.

    In any case, I have marked you response as the answer. 

    bob

    <sub></sub><sup></sup><strike></strike>


    bob mick

    Wednesday, February 15, 2017 12:02 PM
  • Hi Celeste,

    I'm having the same issue. It crashes if it has to delete a sheet with slicers. I tested it taking the slicers manually and then the macro is able to delete the sheet.

    The macro worked great with Office 2010, but not with Office 2016.

    Saludos.

    Monday, June 19, 2017 10:46 PM
  • Hello Saludos,

    This old thread has been closed, I suggest you post a new thread for your issue. And please share a sample code or your workbook so that we could test if it could be reproduced.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 20, 2017 2:43 AM
    Moderator