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

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日: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)
日付のオートフィルタ(AutoFilter)
印刷ダイアログを使用する(xlDialogPrint)
名前定義の一覧と削除(Name)
シートを名前順に並べ替える
数式内の不要なシート名を削除する(HasFormula)
数式の参照しているセルを取得する
増殖した条件付き書式を整理統合する
条件付き書式で変更された書式を取得する
セル結合/解除でセル値を退避/回復
セル結合なんて絶対に許さないんだからね


新着記事NEW ・・・新着記事一覧を見る

抜けている数値を探せ|エクセル雑感(2022-07-01)
.Net FrameworkのSystem.Collectionsを利用|VBA技術解説(2022-06-29)
迷路ネコが影分身の術を体得したら…|エクセル雑感(2022-06-27)
迷路にネコが挑戦したら、どうなるかな…|エクセル雑感(2022-06-26)
サロゲートペアに対応した自作関数(Len,Left,Mid,Right)|エクセル雑感(2022-06-24)
「マクロの登録」で登録できないプロシージャーは?|エクセル雑感(2022-06-23)
オブジェクトのByRef、ByVal、Variant|エクセル雑感(2022-06-22)
コメントから特定形式の年月を取り出す|エクセル雑感(2022-06-19)
4,9を使わない連番作成|エクセル雑感(2022-06-17)
連番を折り返して出力|エクセル雑感(2022-06-16)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.マクロって何?VBAって何?|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




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


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



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