Delete a Name Range that references a spreadsheet that does not exist

[Njoyce]

I have a new job, and one of the spreadsheets that I took over has a named range that refers to a spreadsheet that does not exsist. The name that shows up when I go to /Insert/Name/Define had a box ( [] ).

I have tried this VBA but it does not delete the name range:

Sub RemNamedRanges()

Dim nm As Name

On Error Resume Next
For Each nm In ActiveWorkbook.Names
nm.Delete
Next
On Error GoTo 0

End Sub

[Aussiebear]

Hi Njoyce, Welcome to the forum. I've placed your code within the vba tags to make the reading of the code easier. To do this yourself when posting here, look for the icon with vba written diagonally across it. Select your code and click on this icon.

In relation to your request, have a look at the following http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp

The utility may prove to be useful in your situation.

[tstav]

Hi Njoyse,
I see nothing wrong with the code you posted. It should delete all named ranges of the Activeworkbook (not only the one you're interested in deleting).

Please provide us with the following (or check the following by yourself):
1.The name of your activeworkbook.
2.The name of the workbook that contains the named range you want to delete.
3.The name of the named range.
4.The name of the workbook this named range refers to (you say you can see it in the menu insert>name>define.

According to what you have told us:
The activeworkbook is also the Wbk that contains the named range.
You can see the named range in the menu (insert>name>define).
The Wbk this named range refers to, does not exist.

If the above are all correct, then your code would delete this named range (since it would delete all of them, as I have already said).

As for the brackets [] you say the name had (by the way, why had and not have?):
Are these brackets in the name's reference string? (they just cannot be part of the name's name)
If they are in the reference string you should be seeing something like
='C:\Folder1\Folder2\[Filename.xls]Sheet1'!$A$2:$D$5

[Njoyce]

Thanks for replying,

Please provide us with the following (or check the following by yourself):
1.The name of your activeworkbook.
- rid.xls
2.The name of the workbook that contains the named range you want to delete.
- rid.xls
3.The name of the named range.
- these names are invalid and show as squares and when I try to delete manualy (insert>name>define - highlight one and hit delete) nothing happens
4.The name of the workbook this named range refers to (you say you can see it in the menu insert>name>define.
- they all refer to an workbook that doesn't exist so it has "#REF!" in the name i.e. "='C:\Work\[Template.xls]#REF'!#REF!"

If the above are all correct, then your code would delete this named range (since it would delete all of them, as I have already said).
- the code does delete all named ranges except the ones with invalid names

As for the brackets [] you say the name had (by the way, why had and not have?):
Are these brackets in the name's reference string? (they just cannot be part of the name's name)
If they are in the reference string you should be seeing something like
='C:\Folder1\Folder2\[Filename.xls]Sheet1'!$A$2:$D$5[/quote]
- sorry this was a bad way to try and creat a square or a box that the names has... ()

[lucas]

I'm guessing that you tried to delete it manually with no sucess?
http://www.vbaexpress.com/forum/showthread.php?t=15454&highlight=delete+named+range

[lucas]

Another possibility is that if it is a system generated name sometimes excel responds incorrectly. Can you rename it and then delete it?

http://www.vbaexpress.com/forum/showthread.php?t=16354&highlight=delete+named+range

[tstav]

Your answers are very clear Njoyce and need no second reading. Thank you for that.

Very interesting case. Can you attach the file? Maybe a 'close encounter' would help.

I noticed in your answer that it's not the spreadsheet that's missing, but the Sheet and range references. But the file may also have been deleted, as you said...

[Njoyce]

Thanks all for the help!

I have tried to change the name and delete, with no success...

I have attemped to attach the file let me know if it does not work (I am new to this site)

Thanks,
Nick

[tstav]

Ok Nick,
got the file.

Here's a guess (as wild as it may be).
Could it be that these names have been defined in another language (not a latin based language, like Cyrillic or whatever?). In that case, if your PC lacks the relevant font, this could be causing the weird names.

If no, then have you thought about copy/pasting all sheets (and any code) of this workbook to a new one? (and thus get rid of these names?)
There's a drawback here. You may have other names that you need to keep, plus you have to seriously check that you don't miss out on transferring any important information from sheet to sheet.

PS. To be honest I've never tried that sheet to sheet copy. Does it transfer the names with it? I would guess not.

These were just thoughts I've made.
Regards, tstav

[Njoyce]

Thanks for the ideas, I do not believe that the spreadsheet were defined in another language...

Your second idea is a good one I have started this process for all my spreadsheets going forward. I was just trying to write/find some code that I might be able to run on the 100+ spreadsheets these names exist.

Thanks and let me know if you think of anything else!

[tstav]

(from post#9) I've never tried that sheet to sheet copy. Does it transfer the names with it? I would guess not
Yes it does!
Copying a sheet from one workbook to another by right-clicking the sheet's tab and selecting 'copy', copies over the names that had been created in that sheet, too.
So, be double careful.

[lucas]

I found this thread in a different forum but it describes our problem here.

The link to Jan Karel Pieterse's Name Manager is at the top of the page and it works for deleting these odd names....I tried it.

You can select them all and try to delete them. If they were valid names it would allow you to delete them all at one time but since we aren't that lucky we have to rename them all. It does pop up a simple window for each one in turn so you can just quickly assign each one a new name. Then when it's done you can select them all and delete them all.

The only one it won't let you delete is the system assigned range "Print_Preview" I think it is......

Link to the thread:
http://www.tech-archive.net/Archive/Excel/microsoft.public.excel/2005-05/msg01323.html

A direct link to Jan's Addin Manager.....its fairly close to the top of the page. Second paragraph after Jan's name.
http://www.oaltd.co.uk/mvp/

Not a perfect solution but I haven't seen much better for dealing with this so far. Maybe tstav will bail us out.

[tstav]

The only one it won't let you delete is the system assigned range "Print_Preview" I think it is

Would that be the Print_Area name, Steve?

[lucas]

Yes ts, I'm should have looked before posting. It is the Print_Area name which is a system assigned name.

[lucas]

I don't think the problem has anything to do with the invalid range references. I think its an invalid range name problem. I usually find out that I am wrong though so hopefully someone can find an easier way to deal with this, especially since the poster is looking for a solution for multiple files at one time...

[mikerickson]

This gives the option of deleting any name that evaluates to an error, including names pointing to closed (not nessesarily non-existant) workbooks.

Sub test()
Dim oneName As Name, promptStr As String
For Each oneName In ThisWorkbook.Names
With oneName
If IsError(Evaluate(.RefersTo)) Then
promptStr = .Name & " evaluates to error" & vbCrLf & "Refers To: " & .RefersTo & vbCrLf & "Delete Name?"
If MsgBox(promptStr, vbYesNo) = vbYes Then
oneName.Delete
End If
End If
End With
Next oneName
End Sub

[Cyberdude]

I don't have a solution. I just want to say this about that ...
I am a heavy user of defined names. Several years ago I somehow got a defined name that I wanted to delete, and to this day I still haven't found a way to get rid of it. When I delete it, it disappears, only to reappear the next time I look. I've just learned to live with it. One of life's mysteries.

[david000]

http://vbaexpress.com/kb/getarticle.php?kb_id=717

Ken Puls - Awesome sub!

Sub DeleteRefErrRanges()
'Macro Purpose: To delete any named range with a "#REF!" error in it
Dim nm As Name

For Each nm In ActiveWorkbook.Names
If InStr(1, nm.RefersTo, "#REF!") > 0 Then nm.Delete
Next nm
End Sub

[Njoyce]

Does someone know a way to change this VB so that it does not delete print ranges? Print_Area is the name that has the print range...

I use SQL more than VB and I'm having issues deleting all with out "Print_Area"

For SQL I would say where name <> "Print_Area"

This is the original VB script...

Sub original()

Dim nm As Name

On Error Resume Next
For Each nm In original.Names
nm.Delete
Next
On Error Goto 0

End Sub

[Njoyce]

I have tried this but it did not work...

Sub DltNamesNoPrintRange()

Dim nm As Name

On Error Resume Next
For Each nm In ActiveWorkbook.Names
If nm.Name = Print_Range Then
Resume Next
End If
nm.Delete
Next
On Error GoTo 0
End Sub

[mikerickson]

Sub DltNamesNoPrintRange()

Dim nm As Name

For Each nm In ActiveWorkbook.Names
If nm.Name <> "Print_Range" Then
nm.Delete
End If
Next
End Sub

[Njoyce]

Thanks for the reply, but this also deleted my "Print_Area"...

[mikerickson]

I set a print area and then ran this code

Dim oneName As Name
For Each oneName In ThisWorkbook.Names
MsgBox oneName.Name
Next oneName

One of the names returned was "Sheet1!Print_Area". Running that test loop will show you the print area's full name to be included in the clear names macro.

[lucas]

That works for me now Mike. Print area's are sheet specific so it makes sense when you think about it.

[mikerickson]

Glad to help