How to delete a single named range from a worksheet

[sspatriots]

Hi,

This seems like a silly question, but nothing I've tried seems to work. I have a worksheet called "Jobs" and on that worksheet there is a named range called "HYDROJNRange". I've placed the line of code below in my macro to get rid of it, but I keep getting a Runtime Error 1004 "Application-defined or Object-defined error". Any help on what I am missing here would be greatly appreciated.


Thanks, SS

    ActiveWorkbook.Names("HYDROJNRange").Delete

 

[Fluff]

That works for me, check the spelling of the range.

 

[sspatriots]

It is just a single cell that has that named range. When I select it, I see the named range in the upper left corner that matches what I have in my command line.

 

[sspatriots]

I found the following and set the "InStr" name to "HYDROJNRange" and it worked. This is really strange to me, because I think the original line of code I had should have worked.

Sub DeleteHYDROJNRange()

Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
    If InStr(xName.Name, "HYDROJNRange") > 0 Then xName.Delete
Next


End Sub

 

[Fluff]

Is it Sheet scoped or Workbook scope?

 

[sspatriots]

Is it Sheet scoped or Workbook scope?

I'm sorry, not sure what you mean by that last question?

 

[Fluff]

If you look in the name manager it will show if a Name is worksheet or workbook scope.
I suspect that yours was worksheet scope & that sheet was not active with your original code.

 

[sspatriots]

I looked up what you were asking and it is actually worksheet scoped. I'm guessing it is because the named range is a single cell in a Structured Table in Name Manager. Would that be the case as to why it is worksheet scoped. I see it is greyed out and can't be edited.

I changed that line of code to the following and it worked like you said.

Sheets("Jobs").Names("HYDROJNRange").Delete

 

[Fluff]

I'm guessing it is because the named range is a single cell in a Structured Table in Name Manager

It just depends on how it's setup in the first place.

 

[sspatriots]

Below is the code that creates that named range. What would I do different if I wanted to make this range a workbook scope named range? Not that I need to know this right now, but always like to learn as much as I can.

Sub HYDROJNRange()

    Dim iName As String
    iName = "HYDROJNRange"
    ActiveSheet.Names.Add Name:=iName, RefersTo:=Selection

End Sub