Search code examples
vbaexcelnamed-ranges

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.

Solution

  • 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!