CODEWITHSUNDEEP
vbaexcelnamed-ranges
narfie
narfie

Reputation: 493

VBA 400 Error when trying to Delete a Named Range

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.

Upvotes: 1

Views: 2199

Answers (2)

AJD
AJD

Reputation: 2438

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.

Upvotes: 0

narfie
narfie

Reputation: 493

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!

Upvotes: 1

Related Questions