エクセルVBA名前の定義特定のシートの名前のみ削除

質問

いつもお世話になってます。
Excel2013のVBAより、特定のシート、例えばデータというシートに付けられた名前の定義を消す事は出来るでしょうか？他のシートの名前の定義は残しておきたいです。
ActiveSheet.Namesで指定しても消去されませんでした。

Sub Sample1()
Dim n As Name
For Each n In ActiveSheet.Names
n.Delete
Next
End Sub

回答No.1

keithin · ベストアンサー

昨日のちょっと応用するだけです。

変更前：
　’if n.referstorange.rows.count = rows.count then '適宜応用の事

変更後：
if n.referstorange.parent.name = "消したいシート名" then

理解が必要なのは、ブックレベルの名前とシートレベルの名前は扱いが違う事です。迂闊にシート複写とかしてると、同じ名前でブックレベルとシートレベルが混在してる可能性もあります。どのレベルで名前を定義してるか、よく確認してください。
また注意が必要なのは、同じシートの範囲でも消してよい名前と消したくない名前があるんじゃないのという点です。

必要に応じより詳しい状況を添えて、別途ご相談を投稿してみて下さい。

お礼

どうもありがとうございました。
確認した限りでは問題なく動きました。

今回は、ブックレベルの名前でこのシートは複写等基本行わないので問題ないです。
また、このシートの名前定義は一度全消去してまるごと書き換えるので大丈夫です。

また、他のシートの名前の定義でうまくいかなかったら投稿するかもしれません。そちらは、複写等も行います。

回答No.2

WindFaller

こんにちは。

どちらかというと、昔の感覚では、危ないコードのようです。名前定義は、あまりVBAとは相性良くないようですから、時々、トラブルを起こしたようです。もし、VBAで削除出来なくなったら、手動で削除するようにしてください。

>特定のシート、例えばデータというシートに付けられた名前の定義を消す事は出来るでしょうか？
一応、前回の方のコードに手を入れさせていただきました。

'//
Sub Sample2()
　Dim nm As Name
　Dim dummy As Variant
　Dim c As Variant
　For Each nm In ActiveWorkbook.Names
　　If InStr(nm.RefersTo, ActiveSheet.Name) > 0 Then
　　　nm.Delete
　　End If
　Next nm
　With ActiveSheet
　　For Each c In .Range("C3", .Cells(3, Columns.Count).End(xlToLeft))
　　　　On Error Resume Next
　　　　c.EntireColumn.Name = c.Value
　　　　If Err.Number > 0 Then
　　　　　c.EntireColumn.Name = c.Value & "_"　'名前に設定できない時
　　　　End If
　　　　On Error GoTo 0
　Next c
End With
End Sub

'//

例えば、"c" とか、"r" という名前は使えません。その場合は、"_" アンダーバーが入ります。
また、このマクロには、欠陥があって、同名のセルの名前は、上書きされてしまいます。その場合は、何らかの工夫が必要です。そういうケースがあるときは、かならず、お礼欄に一言、お書きください。ここにアクセスしなくても、メールが届いて分かりますから。

お礼

どうもありがとうございました。
質問の動作について確認しましたが、問題なく動作しました。
前回の質問のエラー処理もしていただきありがとうございました。

MsgBox　 Range(Cells(3, i).Address)

を入れて、使えない名前を変えるようメッセージを出すようにしています。

上書き問題の事は考えていませんでした。
試しに他のシートのセルに名前をつけて、このマクロを動作させると他のシートにつけた名前が消えていました。
このシート内に定義した名前の上書きは問題ないのですが、別のシートに設定していた名前の上書きは少し唸りますが、おそらく別シートでこのシートで使うような名前を付ける事はないだろうと思うので大丈夫だと思います。
また、そういうケースが発生した場合にお尋ねするかもしれません。

どうもありがとうございました。