Delete ranged names

[ShoYnn]

So I have an interesting one for you all.

I have a master workbook I made for a gradesheet and put it in the public folder. When a new class starts the instructor will make a new folder for that class and copy the master book into that folder. It is still in beta mode, so as the classes have been going on the instructors have been making requests and suggestions for changes to the gradebook. I typically make the changes to the master and tell the active ones to suffer until next round, unless jt is an actual bug, but this one time I made the changes only to the active class as sort of a pilot to see if the instructors likex the new way or old way better. At the end of the pilot they liked the new way better, so instead of recreating the changes in the master book I just copied the sheets from the active class to the master book and made sure all of the formulas were fixed so they didn't reference the active class book and all was well. The one thing I forgot to do was remove the named ranges that got copied over to the master book, so now in my master book I have multiple named ranges that have the same name, but one will be referencing the madter book which is good, and one will be referencing the active class book which is not good. I have since then tried to delete the named range doppelgangers that reference the active class book, but they are completely greyed out, and it doesn't matter if I have both gradebooks open and fully unlocked, I cannot seem to delete them. Does anyone know how to delete a named range that references a separate workbook? Thanks for any help you can provide!

P.S. I totally tried vba
Sub delete_names
ActiveWorkbook.Names("sheet name").Delete
End Sub
and this did not work, gives me a 1004 error.

 

[James006]

Hello,

You could test following

Sub DeleteNamedRanges()
Dim NRng As Name
For Each NRng In Names
    ActiveWorkbook.Names(NRng.Name).Delete
Next NRng
End Sub

Hope this will help

 

[ShoYnn]

A valiant effort, but no luck. That didn't even touch any of the named ranges. Thanks for the reply though!

 

[James006]

Strange ...

Just tested it out ... and it deleted all the named ranges ...

Do you still see all the names with Control F3 ...?

 

[ShoYnn]

Yeah, all of the ranges still exist, not a single one was deleted ?

 

[ShoYnn]

Well, some good news, after running that code a couple of times with no luck I went through and triple verified that all of the sheets were unlocked, I even made all of my behind-the-scenes hidden sheets visible again and verified they were unlocked, and after doing this I was able to delete the Named Ranges I needed to. Funny thing was that I tried this before, so maybe I missed one sheet when I went through before unlocking everything, or maybe running your code changed something, who knows. Good news is that the problem is resolved! Thanks for the help!

 

[James006]

Glad you could fix your problem