名前定義の一覧と削除（Name）

公開日：2013年5月以前　最終更新日：2019-10-27

名前定義の一覧と削除（Name）

名前定義は使い方によっては、とても便利な機能ですが、
長く使っているブックでは、とても多くの名前定義が入ってしまっていたり、
参照エラーを起こしている名前定義が多数あったりと、管理に困る場合も多々出てきます。

これらが発生する原因としては、
ブック間のシートコピーで増えていってしまったり、
シートおよびセルの削除によって参照エラーになったままにしておくことで、
後々に見た時に何の名前定義かさえも分からなくなってしまったりします。

名前定義の一覧を取得し、シートに書き出すマクロVBA


  
    
      Sub sample()

      　　Dim nm As Name

      　　Dim i As Long

      　　i = 1

      　　For Each 
nm In ActiveWorkbook.Names

      　　　　Cells(i, 1) = nm.Name

      　　　　Cells(i, 2) = "'" 
& nm.Value

      　　　　Cells(i, 3) = nm.Parent.Name

      　　　　i = i + 
1

      　　Next

      End Sub

Cells(i, 1) = nm.Name
名前定義の名前です。

Cells(i, 2) = "'" & nm.Value
名前定義の参照範囲です。
.Valueは.RefersToや.RefersToR1C1等でも良いです。

Cells(i, 3) = nm.Parent.Name
範囲がブックと、シートがありますので、
親オブジェクトを出力しています。
名前の範囲がブックの場合はブック名、シート範囲の場合はシート名が出力されます。

非表示の名前定義を表示


  
    
      Sub sample2()

　　Dim nm As Name

　　For Each nm In ActiveWorkbook.Names

      　　　　nm.Visible = True

　　Next

End Sub

nm.Visible = True
これで表示しています。
このVBAでは、元が表示/非表示に関わらず全て表示にしています。

名前定義を削除する


  
    
      Sub sample3()

      　　Dim nm As Name

      　　On Error Resume Next

      　　For Each nm In ActiveWorkbook.Names

      　　　　nm.Delete

      　　Next

      　　On Error GoTo 0

      End Sub

古くから引き継いでいるブック等では、削除できない名前定義が存在していることが多々あります。
その時には、エラーになってしまう為、
On Error Resume Next
これで、エラーを無視するようにしています。

VBAで削除できずに残ってしまう名前定義は、先の非表示の名前定義を表示を実行してから、エクセルの名前の管理で削除してみてください。

全てではなく、参照エラーとなっている名前定義だけ削除する場合は、


  
    
      Sub sample4()

　　Dim nm As Name

　　On Error Resume Next

　　For Each nm In ActiveWorkbook.Names

　　　　If nm.Value Like "*[#]REF!*" Then

　　　　　　nm.Delete

　　　　End If

　　Next

　　On Error GoTo 0

End Sub

参照範囲の文字列を判定することで、他ブックを参照している名前定義だけを削除する等、いろいろなパターンに変更可能でしょう。

さらに極めてまれですが、
ブックの共有等（原因ははっきりしませんが）で、数万以上の名前定義が存在していることがあります。
そのようなブックにおいては、For Eachがエラーとなってしまい処理できない場合もあります。
このような場合は、以下のようにしてください。


  
    
      Sub sample5()

　　Dim i As Long

　　On Error Resume Next

　　For i = 1 To ActiveWorkbook.Names.Count

　　　　ActiveWorkbook.Names(i).Delete

　　Next

　　On Error GoTo 0

End Sub

これでも削除できないような場合は、VBAでの対応は難しいと思われます。

サイト内の関連ページ

第92回.名前定義（Names）｜VBA入門

・Namesコレクション・Nameオブジェクト・RangeオブジェクトのNameプロパティ・名前定義を使ったRangeの書き方・シートコピー時は名前定義に注意・名前定義の実践例

指定セルに名前定義されているか判定する

・指定セル範囲が何らかの名前定義に含まれているか・指定セル範囲と同一範囲の名前定義があるか・すでに設定されている名前定義を削除してから再設定・マクロVBAでの名前定義について

同じテーマ「マクロVBAサンプル集」の記事

オートフィルター（AutoFilter）

エクセルでは、定番機能のフィルターです。「Sheet1」のA列でフィルターし、「Sheet2」へコピーします。ごく基本的なフィルターです。フィルターのセル範囲指定は、いろいろな指定が可能です。「連続セル範囲の選択」も参考にして下さい。

日付のオートフィルタ（AutoFilter）

とても便利なオートフィルターですが、日付となると、結構大変です。以下の表で説明します。普通は、こんなように指定します。Operator:=xlFilterValues は2007以降で追加された機能です。

印刷ダイアログを使用する（xlDialogPrint）

入力しやすいように、セルに色をつけている事が多いと思いますが、印刷時には、ちょっとじゃまな場合もあります。インク（トナー）も無駄ですしね。印刷時に色指定を解除して印刷する方法になります。Application.Dialogs(xlDialogPrint).Show 印刷タイアログを表示します。

名前定義の一覧と削除（Name）
シートを名前順に並べ替える

シートを名前順に並べ替える方法になります。配列を使っていますが、配列が難しい場合は、シートに書き出して処理すれば良いでしょう、並べ替えもシート上なら簡単です。上記では、シート名を文字列として処理しています。

数式内の不要なシート名を削除する（HasFormula）

複数のシートにまたがる数式を入力していると、自身のシート名!が数式についてしまいます、この自身のシート名!は不要であり、式を見づらくしてしまいます、この不要なシート名を、マクロVBAで一括削除します。ブックの全シート、全セルを対象として、数式の中から、自身のシート名!を削除するマクロVBAになります。

数式の参照しているセルを取得する

セルに入っている数式の参照しているセルを取得するには、RangeのPrecedentsプロパティを使いますが、このプロパティは他のシートの参照には対応していません。また、セルの参照先を取得するプロパティには、Dependentsプロパティがあります。

増殖した条件付き書式を整理統合する

・条件付き書式の増殖に関する、Microsoft サポート・増殖した条件付き書式の実例と対応・簡単なVBAでの対応・VBAで条件付き書式を整理統合した結果・今回のVBAコードの発想について・Application.ConvertFormulaメソッド・増殖した条件付き書式を整理統合するVBA ・条件付き書式で設定できる書式・増殖した条件付き書式を整理統合するVBAの使い方・増殖した条件付き書式を整理統合の最後

条件付き書式で変更された書式を取得する

条件付き書式が設定されている場合、当然ですが見た目は、本来そのセルに設定されている書式ではなく、条件付き書式の条件によって設定されている書式になります。VBAで、この条件付き書式によって設定された書式を取得します。これが取得できるようになったのは、Excel2010からですので、このページで紹介するVBAコードはE…

セル結合/解除でセル値を退避/回復

・セル結合/解除でセル値を退避/回復のVBA ・退避したセル値の全削除と一覧出力・セル結合/解除の最後に

セル結合なんて絶対に許さないんだからね

セル結合の弊害はネットに溢れているのでここで改めて説明の必要はないでしょう。とはいえ、ついついセル結合してしまう事ありますよね、人間だからね。VBAで適切に処理すればセル結合もきちんと処理は可能です。

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
マクロVBA応用編
マクロVBAサンプル集
名前定義の一覧と削除（Name）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBAサンプル集
名前定義の一覧と削除（Name）