Deleting defined name results in Runtime error 1004

smiley3141

Board Regular
Joined
Aug 13, 2010
Messages
77
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

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
Hello,

Try to first delete the name and then the range.

something like:


Code:
[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]
 
Last edited:

smiley3141

Board Regular
Joined
Aug 13, 2010
Messages
77
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
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

Board Regular
Joined
Aug 13, 2010
Messages
77
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,467
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

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
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

Well-known Member
Joined
Dec 21, 2009
Messages
1,885
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

Board Regular
Joined
Aug 13, 2010
Messages
77
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

Board Regular
Joined
Aug 13, 2010
Messages
77
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.
 

Forum statistics

Threads
1,082,040
Messages
5,362,804
Members
400,693
Latest member
jenlj

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top