Results 1 to 9 of 9
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Slow delete of names (Excel 2003)

    I have 93 names in a workbook and I have code that copies one sheet into its own workbook. Since the names of the parent get sucked into this new workbook automatically, I want to delete those names before saving it. I find that it takes 7 seconds to delete these 93 names <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> It's just a simple FOR loop that goes through Activeworkbook.Names and deletes them. Is there a one-shot method for this like Clear? I can't find any other way to delete these names.

    Why would it take so long to delete names? The new workbook isn't protected, just one sheet. The new one doesn't need these names and I don't want the user to see them either. I do a <Sheetname>.Copy to make the new workbook, should I create this new workbook a different way like create it first then copy/paste the cells? This actually wouldn't work well either as I have charts (actually pix of charts done with camera tool) I need to copied too and how would I know, programmatically, where to place them on the new worksheet? So that's why I copy the entire sheet since I already have it laid out like I want it.

    Thnx, Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Slow delete of names (Excel 2003)

    Do you use a For Each loop, or a For i = 1 To ... loop, or a For i = ... To 1 Step -1 loop to delete the names?

    (I'm not aware of a method to delete all names in one fell swoop)

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow delete of names (Excel 2003)

    It's a FOR EACH

    dim nm as Name
    for each nm in activeworkbook.names
    nm.delete
    next nm

    I'll try FOR k=1 ....

    Deb

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow delete of names (Excel 2003)

    Well it still takes too long, 5 seconds for 93 names (and this WinXP, 2GHz CPU, 2GB memory)
    <pre> Debug.Print "1: " & Now
    For k = ActiveWorkbook.Names.Count To 1 Step -1
    ActiveWorkbook.Names(k).Delete
    Next k
    Debug.Print "2: " & Now</pre>

    Oh well, nothing I can do about it... Craziness. I can't just copy/paste the cells because I have objects too (charts, pix, etc.) and I don't know how to place those on the right spot on new sheet if I copy/pasted them via code. Well, technically yes I could remember the cell address for each item and paste it there but, yeeesssh, this is nuts for what was suppose to be a simple 'copy sheet to new workbook' <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> Oh well.... the users will just have to be patient.

    Thnx,
    Deb

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Slow delete of names (Excel 2003)

    I can only imagine that the delay is associated with recalculating the rest of the workbook. Can you temporarily turn off automatic recalculation before and then turn it back on after doing the name purge?

    If you select all cells in the range, does a .Clear or .Delete become available?

    Alternately, is there any way to fill the entire range with vbNullSting?

    (I should declare that I know very little about Excel, so these are based on "Word-think". <img src=/S/grin.gif border=0 alt=grin width=15 height=15> )

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow delete of names (Excel 2003)

    Actually that's a good idea... I tried it but it didn't matter.

    I ended up doing copy/pastevalue but I needed to also copy the color palette from the parent workbook (since I changed the default) and that takes 14 seconds!! So combined with the 5-9 seconds to delete the names the user can take a nap before this new workbook is ready. <img src=/S/doze.gif border=0 alt=doze width=15 height=15>

    Oh well, I've already overdue to have this thing finished and can't fret about it anymore.... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Thanks all for the ideas.

    Deb

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Slow delete of names (Excel 2003)

    Perhaps

    Application.EnableEvents = False

    and after the core code has run

    Application.EnableEvents = True

    may also help.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow delete of names (Excel 2003)

    Ok final analysis showed that it was updating the color palette that took so darn long. I did turn off calcs as well just in case.
    <pre>ActiveWorkbook.Colors = Application.Workbooks(parentWB).Colors ' very, very slow</pre>

    I decided to heck with using these custom colors on the new workbook (it'll most likely be used as an email attachment) and so after changing the colors to 'normal' the copying of this worksheet, deleting names, etc. took a few seconds.

    Thnx,
    Deb

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Slow delete of names (Excel 2003)

    Probably setting Calculation to Manual before removing the names will speed up things a lot.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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