ExcelvbaRefersToRange名前削除

質問

いつもお世話になります。
指定シートの名前を削除するコードを教えていただき、試していたのですが、あるブックの時エラーになり、
: RefersToRange : <アプリケーション定義またはオブジェクト定義のエラーです。> : Range
というエラーになっていました。

ウィンドウで中身をみると、下記のコードのnの中のRefersToとかの値がまったく違うブックの名前になっており、最後に#REF!がついていたのでこれが原因なんだろうと思いますが、どうすればよいか全く分かりません。

一応、見えない名前の定義を消去するようなコードをかけて、#REF!になっていいる名前の定義を３00ほどでてきたのを消しています。
リンクの編集のコマンドもグレーになっているので、リンクもないと思います。
もともと、そのブックを改造したんだと思います。
正常に動作するものは、きちんと自分のブックのシート名になっていました。
何か方法はありますでしょうか？

Sub sumple()
Dim n As Name
For Each n In ActiveWorkbook.Names
If n.RefersToRange.Parent.Name = "データ" Then
n.Delete
End If
Next n
End Sub

回答No.1

WindFaller · ベストアンサー

こんにちは。

>見えない名前の定義を消去するようなコードをかけて、#REF!になっていいる名前の定義を３00ほどでてきたのを消しています。

前回、私が言いかけた件ですが、日経BP21 の芳坂さんが、ここの3－4で、この問題について触れています。
http://hp.vector.co.jp/authors/VA016119/kitan01.html#4

そのコード自体は、前回のように、On Error Resume Next ～ On Error Goto 0 で挟んで、エラーが出たら削除してしまう、という方法でよいのですが、今回の件は、意外に複雑な問題が含んでいるようなので、あまり自信はありません。

少し、手を入れてみました。
'//
Sub Sample1()
Dim n As Name
　For Each n In ActiveWorkbook.Names
　On Error Resume Next
　If n.RefersToRange.Parent.Name = "データ" Then
　　n.Delete
　End If
　If Err.Number > 0 Then
　　n.Delete
　End If
　On Error GoTo 0
　Next n
End Sub
'//

お礼

こんばんは。どうもありがとうございます。

自信がないとの事でどうかな？と思いましたが、このコードで一度動かすと元のコードでも正常に動きました。また、他のブックの名前などもなくなっていました。
とりあえず解決です。今度、変な症状が現れたら引っ越しも考えようと思います。

回答No.2

keithin

＞どうすればよいか全く分かりません。

Sub sumple()
Dim n As Name
on error resume next
For Each n In ActiveWorkbook.Names
If n.RefersToRange.Parent.Name = "データ" Then
n.Delete
End If
Next n
End Sub

とかで動く範囲で動かせば、通常は用事は足ります。

＞リンクの編集のコマンドもグレーになっているので、リンクもないと思います。

名前の定義の管理を確認して、要らない名前定義が残存してないか確認します。

＞#REF!になっていいる名前の定義を３00ほどでてきたのを消しています。
　：
＞もともと、そのブックを改造したんだと思います。

ゴミが溜まりまくった不具合ブックは、早々に見切りをつけた方が安心ですね。
名前の定義を全て削除するマクロは一連のご相談の最初で出てきましたので、まずはそれを使います。
あとはまっさらのブックを用意、今使ってるブックの「表範囲を」コピーしてまっさらブックに引っ越します。手抜きせず丁寧に手を動かしていけば、問題は無くなります。

一連のご相談の途中でも回答しておきましたが、ブックレベルの名前とシートレベルの名前をしっかり管理/理解しなければいけません。その両方を処置するサンプルマクロも回答済みですので、もう一度ご自分の一連のご相談を見直して下さい。

お礼

長い間色々な人が使ってきているんで、色々汚れてるんでしょうね。
一応、No.1さんのコードで1度動かすと症状は改善しました。
今度、変な問題が起こったら引っ越しも考えてみます。
どうもありがとうございました。