Warning message after Update links

[musicgold]

Hi,

I am using the following code to get rid of name ranges in an excel file. The code seems to work fine for all name ranges except two: Print_Area and Print_Titles. Both these names refer to a weblink. I don't know why my macro is not working for these two names.
I have to remove names from several files and don't want to do that task manually.


Sub Remove_names()

Dim nm As name
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm
End Sub


Can you please suggest a solution?

Thanks,

MG.

[Bob Phillips]

Can we see the workbook.

[musicgold]

xld,

Thanks. Please see the attached workbook.

MG.

[Bob Phillips]

That is very weird, I can't even delete them with NameManager. No errors or anything, it just doesn't work.

The only way that I could delete them was by doing it manually (Insert>Name>Define ... Delete). Eeven recording this as a macro and trying to run the macro didn't work. It just doesn't seem to work from VBA.

I don't know how you defined those names, normally a Print_Area name is local to the sheet it applies to. How you linked it to something on a web page is beyond me, but seems to be the root of the problem.

[GTO]

That is very weird

Nicely understated; very bizarro!


The only way that I could delete them was by doing it manually

Hi Bob,

I found that even AFTER deleting manually, the web ref is still actually there. (Close, open, check)

I tried...

Sub Names_DEL()
Dim _
wb_Names As Name, _
wbNew As Workbook

For Each wb_Names In ThisWorkbook.Names
If wb_Names.Name = "Print_Area" Then
wb_Names.RefersTo = "Summary Stat!$A$1"
End If

If wb_Names.Name = "Print_Titles" Then
wb_Names.RefersTo = "$1:$1"
End If

Next

ThisWorkbook.Save

End Sub

...which seems to 'stick', but by golly, later delete (names) and we're back to web ref.

I even tried the above, after which copy the sheet to a new wb and all, still same thing...

I would suspect the wb is partially corrupt. I believe Chip Pearson had some possible solutions if the WB is worth the effort at saving.

A good day to all, this lad is WAY past stoopid tired,

Mark

[Bob Phillips]

I found that even AFTER deleting manually, the web ref is still actually there. (Close, open, check)

Not for me. I deleted them, saved the wb, closed it, opened it again ...no names.

[GTO]

Well... I may well have fouled up :-(

Gloriowsky though, it is beyond late for me. Have a good afternoon/evening (actually to both you and yours) and yackatchya later,

Mark

[musicgold]

I don't know how you defined those names, normally a Print_Area name is local to the sheet it applies to.
I did not define those names. They came with that sheet.

Thanks.

[Cyberdude]

For what it's worth I have had 3 or 4 of those "won't go away" names that I have just learned to live with and ignore. NOTHING that I've tried has ever worked. It's just some sort of corruption I guess.