1

I want to delete all global named ranges in a workbook. I have this code:

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

Even when I change the code to delete ALL named ranges, I still get the same error.

Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
    xName.Delete
Next

When I run it, only the first Named Range is deleted and then it throws a 400 error. It doesn't move on to the next one.

Is there some sort of add-on or setting that I'm missing? This code seems pretty straight-forward, but I can't get it to work.

Improve this question
16
  • I don't think it's illegal 'cause if I replace the xName.Delete with a msgbox, it displays all of my global named ranges as expect. The problem is NOT with the Instr() bit, it's with xName.Delete. As I mentioned, even without the InStr() function it still behaves the same. Commented Mar 31, 2018 at 9:14
  • That's hardly the point. Commented Mar 31, 2018 at 9:16
  • What are you actually trying to delete? Is there a different pattern? Commented Mar 31, 2018 at 9:16
  • Please don't focus on the "!". I've removed it and the problem persists. So, clearly that is NOT the problem. The "!" isn't actually in the name. That just happens to help me find the global named ranges. None of my Named Ranges have "!" in the actual name. I copied that script from somewhere and that works to find all the global Named Ranges. Commented Mar 31, 2018 at 9:17
  • put a breakpoint in If InStr(xName.Name, "!") = 0 Then xName.Delete and once there type ?xName.Name, xname.RefersTo in Immediate Window and press return . Commented Mar 31, 2018 at 9:23

2 Answers 2

Reset to default
1

Ok, after a long chat and some trial and error I've found the problem. It's actually two-fold.

The reason this code didn't work, seems to be because the first Named Range it found was an Excel built-in range which obviously couldn't be deleted. This is the case with both code snippets

Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
    xName.Delete
Next

AND

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

Because both of these find that built-in Named Range "_xlfn.CONCAT" and tries to delete it.

I finally heeded @QHarr's advice and used a different qualifier. Luckily all my Named Ranges in the Workbook Scope has "Master" in the name, so it was easy enough.

So, the final solution:

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

Thanx guys!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Easy solution is to check the first character of the named range. Special ranges start with "_" (underscore).

The way I solved this was a simple check. And working backwards.

Dim xName As Name
Dim xNum as Long
For XNum = Application.ActiveWorkbook.Names.Count To 1 Step -1
    Set XName = Application.ActiveWorkbook.Names(XNum)
    If InStr(xName.Name, "!") = 0 And Left(XName.Name, 1) <> "_" Then xName.Delete
Next

In my case, I was deleting all names, so did not need InStr(xName.Name, "!") = 0 And but this is very similar.

Improve this answer

5 Comments

Thanx for the suggestion, but unfortunately it doesn't work for me. Also... there's a small error in your code - you've left out a parameter in the Left function. Left(xName.Name <> "_", 1)
@narfie: fixed that typo.
@narfie: should work. Did you amend your code to work backwards? Iterating forwards through a collection and deleting items will generally not work properly because once you delete the first item all the other items are renumbered and you end up skipping items.
Should be If InStr(xName.Name, "!") = 0 And Left(xName.Name, 1) <> "_" Then or the left statement isn't valid
@MyDaftQuestions: Fixed - typo when fixing typo!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.