Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Deleting defined name results in Runtime error 1004

  1. #1
    Board Regular
    Join Date
    Aug 2010
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Deleting defined name results in Runtime error 1004

    Hello, everyone.

    I am trying to delete a named range and then delete the defined name also, so that my name manager is not full of old names. I recorded my actions while trying to delete a range called "Area2."

    The code that was recorded was:

    Sub Delete_Named_Range()
    Application.Goto Reference:="Area2"
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.Names("Area2").Delete
    End Sub

    Now each time I try to run this, the range is deleted correctly, but I get a "Runtime error '1004' Application defined or object-defined error." I imagine it has something to do with the range it refers to being deleted, but am not sure how to correct for this.

    Does anyone have any ideas?

    Thanks for your help.

  2. #2
    Board Regular repairman615's Avatar
    Join Date
    Dec 2009
    Location
    Tennessee, USA
    Posts
    1,885
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting defined name results in Runtime error 1004

    Hello,

    Try to first delete the name and then the range.

    something like:


    Code:
    Sub Delete_Named_Range()
     Application.Goto Reference:="Area2"
     ActiveWorkbook.Names("Area2").Delete
     Selection.Delete Shift:=xlUp
    End Sub
    Last edited by repairman615; Jun 27th, 2012 at 09:29 PM.
    ___-Jeff______________

    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Help using VBA Code
    - Helpful links from Hiker95

    Code Tags:
    [CODE] (Place your code here) [/CODE]
    ___-2007-

  3. #3
    Board Regular
    Join Date
    Aug 2010
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting defined name results in Runtime error 1004

    Repairman 615,

    I just tried that and it still ends up with a runtime error 1004 message on the

    ActiveWorkbook.Names("Area2").Delete

    line. This time it deleted neither the defined name or the range.

  4. #4
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    3,995
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting defined name results in Runtime error 1004

    I just tried that and it still ends up with a runtime error 1004 message
    There's no longer a named range called "Area2" in the active workbook to delete because your code has already deleted it!!

  5. #5
    Board Regular
    Join Date
    Aug 2010
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting defined name results in Runtime error 1004

    Sorry, Trebor76. That is not the answer. When I ran my test, I changed the code to "Area4" which IS defined, and it neither deleted the area or the name.

  6. #6
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    3,995
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting defined name results in Runtime error 1004

    Then either the tab the named range is referring to has been deleted or (more probably) the named ranges have become corrupt i.e. the Name Manager dialog (Ctrl + F3) is displaying #REF! for the named ranges as all the rows it was originally referring to have now been deleted.

  7. #7
    Board Regular repairman615's Avatar
    Join Date
    Dec 2009
    Location
    Tennessee, USA
    Posts
    1,885
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting defined name results in Runtime error 1004

    Hello smiley3141,

    I have just tested your original recorded code and it worked. Basically what I did was select an arbitrary range, filled that with some contents, then named it area2.

    After running the original code the range was deleted and the name removed... I am trying to think of what the issue might be.

    Are there many names within the manager?
    ___-Jeff______________

    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Help using VBA Code
    - Helpful links from Hiker95

    Code Tags:
    [CODE] (Place your code here) [/CODE]
    ___-2007-

  8. #8
    Board Regular repairman615's Avatar
    Join Date
    Dec 2009
    Location
    Tennessee, USA
    Posts
    1,885
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting defined name results in Runtime error 1004

    Trebor may be on to something... If I run the code twice when the name is not present in the name manager, then I do get a error code 1004. That code reads 'reference not valid' which is different than the error code you stated in the OP.

    Just adding to the pool of thoughts.
    ___-Jeff______________

    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Help using VBA Code
    - Helpful links from Hiker95

    Code Tags:
    [CODE] (Place your code here) [/CODE]
    ___-2007-

  9. #9
    Board Regular
    Join Date
    Aug 2010
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting defined name results in Runtime error 1004

    Trebor76,

    After deleting the named region, the name manager box DOES show a #REF! error, and that is what I am guessing might be leading to the problem. That being said, even when recording the macro, that would have been true and it still allowed me to delete the name, so I am not sure what might be the cause.

    Repairman615,
    Yes, I have many defined names. In this example, the "Area2" is scoped to the worksheet it is on, if that makes a difference, while some others (all names are unique) are scoped to workbook or their respective sheets.


    Also, I tried the same macro in a different workbook and get the same error.

    If it matters, I am using Excel 2007.

    Thanks for any suggestions you have.





  10. #10
    Board Regular
    Join Date
    Aug 2010
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Deleting defined name results in Runtime error 1004

    Trebor and repairman,

    I do not think it has anything to do with deleting the same area twice, because I have tried to delete other areas (substituting the appropriate name) AND also recreating another Area2 and naming it. My original code always deletes the named range, but not the name itself. The code suggested by Repairman always selects the named range and then neither deletes the named range or the name. Since it always selects the named range, I do not think it can be a problem with the named range not existing or being incorrectly identified.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com