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]

Give this a try, similar yet a slightly different approach.

[font=Franklin Gothic Medium][color=darkblue]Sub[/color] Delete_Named_Range()
[color=darkblue]Dim[/color] rngREF [color=darkblue]As[/color] [color=darkblue]String[/color]
[color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] errHandler:
     rngREF = ActiveWorkbook.Names("Area2").RefersTo
    [color=green]'MsgBox rngREF[/color]
        Range("Area2").Delete Shift:=xlUp
        ActiveWorkbook.Names("Area2").Delete
errHandler:
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
        [color=green]'MsgBox "The name does not exist and the area can not be selected.", vbInformation, "Error"[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color][/font]

 

[smiley3141]

Repairman615,

I have tried your code and nothing happens. It neither selects nor deletes anything. Also, it does not tive me any error messages.

 

[repairman615]

This has me stumped. As it does delete the name from the manager and the contents of those cells shifting them up for me.

The only thing to my knowledge that could cause nothing to happen is that the name does not exist. If it does than I am doubly stumped.

Basically I am hoping it might be misspelled or a case discrepency.

So my question is does it exist and if so is it exactly the same?

 

[smiley3141]

Repairman615,

I found out why it did nothing. I did not get rid of the apostrophe before your two message box statements. I made sure there was a range named "Area2." I then put "Area2" into my original code and then copied and pasted "Area2" into your latest code to make sure the names are all the same.

When I ran your code I got a "the name does not exist and the area cannot be selected message." BUT I then immediatly ran my original code and it deleted the "Area2" range but would not delete the name. I'm stumped too.

 

[Trebor76]

If you delete all the rows from a named range it will only work once, as a range cannot have no rows in it. The code will error out from then on.

Using the On Error Resume Next command does nothing to fix the error, it merely stops the error message from appearing.

 

[Trebor76]

Try this which checks for the existence and validity of a particular named range before trying to do anything with it:

Option Explicit
Sub Macro4()

    'http://www.mrexcel.com/forum/showthread.php?643406-Deleting-defined-name-results-in-Runtime-error-1004/page2

    Dim rngMyRange As Range
    
    On Error Resume Next
        Set rngMyRange = Range("Area2")
    On Error GoTo 0
    
    If rngMyRange Is Nothing Then
        MsgBox "Either there's no range named called ""Area2"" in the workbook or it's corrupt with ""#REF!""!!" & vbNewLine & "Investigate and try again.", vbExclamation, "My Range Name Editor"
        Exit Sub
    Else
        MsgBox "Code here to do whatever you need to with the named range ""Area2""."
    End If

End Sub

 

[smiley3141]

Trebor76 and Repairman615,

Finally, I know what is causing the problem, but not how to solve it. I have tried my original code, and both of the codes Repairman615 suggested and they all work on names that are scoped as workbook level names. The names of my ranges, however, are scoped to the worksheet they are on. I am not sure why the macro recorder did not pick up and record anything regarding the scope, or how to change the code to correct for this, but that appears to be the problem.

I just recorded code when I create two named ranges, one scoped to the workbook level and one to the worksheet level, and I do not see a difference in the code. I'm stumped on how to either create or delete specifically worksheet level names.

Any ideas?

 

[smiley3141]

Just solved the problem. The original code was:

[COLOR=#333333]Sub Delete_Named_Range() 
[/COLOR]
[COLOR=#333333]Application.Goto Reference:="Area2"[/COLOR]
[COLOR=#333333]Selection.Delete Shift:=xlUp[/COLOR]
[COLOR=#333333]ActiveWorkbook.Names("Area2").Delete
[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

This code would not delete my names that are scoped to a worksheet. The following code does:

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

End Sub

Trebor76 and Repairman615, thank you very much for your help.

 

[repairman615]

Nice, glad you worked it out!