Deleting defined name results in Runtime error 1004

[smiley3141]

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.

 

[repairman615]

Hello,

Try to first delete the name and then the range.

something like:

[FONT=Franklin Gothic Medium][COLOR=darkblue]Sub[/COLOR] Delete_Named_Range()
 Application.Goto Reference:="Area2"
 ActiveWorkbook.Names("Area2").Delete
 Selection.Delete Shift:=xlUp
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

 

[smiley3141]

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.

 

[Trebor76]

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!!

 

[smiley3141]

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.

 

[Trebor76]

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.

 

[repairman615]

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?

 

[repairman615]

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.

 

[smiley3141]

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.

 

[smiley3141]

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.