【VBA入門】不要な「名前の定義」を一括削除する方法|エラー・非表示の名前もマクロでスッキリ

Excelブックを仕事で使っていると気づかぬうちにいろんな残骸が増えてきます。

みなさん、こんにちは。セルネッツ竹本です。
今回は、「名前の定義を一括削除」をテーマに、解説をしたいと思います。

結論から言うと、まずは「名前の管理」(Ctrl+F3)で「エラーのある名前」に絞り込み、まとめて削除するのが安全です。
数が多い場合はマクロで一括削除できますが、すべての名前を消すと、数式や印刷範囲まで壊れることがあります。

この記事では、次のことがわかります。

• 不要な名前の定義が増える原因と、「名前の管理」での削除手順
• 名前の定義をVBAで一括削除するコードと、消すと壊れるもの
• エラーの名前だけを消す・非表示の名前を表示する安全なマクロ

名前の定義とは?不要な名前が増える原因

名前の定義は、セル範囲や値に「単価」のような名前を付け、数式や入力規則で使えるようにする機能です。
数式に直接セル番地や数値を書かずに済むので、上手に使えばブックがわかりやすくなります。名前を使ってハードコーディングを避ける考え方は、ハードコーディングのメリットとデメリットで紹介しています。

一方で、次のような操作をくり返すと、使われない名前が気づかないうちに溜まっていきます。

• 名前の付いたシートを、別のブックへコピー・移動する
• 別のブックから、名前を使ったセルを貼り付ける
• 名前の参照先のシートや行を削除する(参照先が「#REF!」になった名前が残る)

大量な残骸はExcelブックSizeや不安定さに影響するので注意

実は、Excelブックには、あってはならない「残骸」と無い方がより良い残骸があります。

問題はその影響なのですが、マクロ開発において、プログラム処理や実行速度に影響を与えるケースも多いので、「健康状態チェック」は欠かせません。

今回は、かなりの頻度で増えやすい「名前の定義の一括削除」Moduleを紹介します。

ブックが重い・不安定になったときの症状と対処は、「メモリが不足しています」と表示されるときの対処法もあわせてご覧ください。

「名前の管理」で手作業でまとめて削除する

数十件までなら、マクロを使わずに「名前の管理」画面から削除できます。

1. 「数式」タブの「名前の管理」を開く(ショートカットはCtrl+F3)
2. 右上の「フィルター」から「エラーのある名前」を選ぶ
3. 先頭の名前をクリックし、Shiftキーを押しながら最後の名前をクリックして、まとめて選ぶ
4. 「削除」を押し、確認の画面で「OK」を押す

ただし、非表示に設定された名前は、この画面に表示されません。非表示の名前を表示させるマクロは、後で紹介します。

このModuleで一括削除できます。

数が多いときは、ブック内の名前をすべて削除する次のマクロが使えます。

念のため、ひとつずつ名前を確認しながら削除するほうが望ましいです。

(Debug.Print S_名前定義.Name で一つずつ名前を調べることができます。)

が、膨大な個数がある場合は、それも現実的ではないので、名称チェックなどができると良いですね

それがない場合は、仕方ありません。

Dim S_名前定義 As Name

'※※※※※※※※※※※※※※※※※※※※※
Sub M_名前定義を削除()
'※※※※※※※※※※※※※※※※※※※※※

On Error Resume Next

    '★名前定義を全削除(名前を関数その他に有効活用している場合はここは削除)
    For Each S_名前定義 In ActiveWorkbook.Names
        S_名前定義.Delete
    Next

End Sub

「名前の定義」だけでなく、いろんな種類の残がいが考えられるので、ときおりチェックすることをおすすめいたします。

すべて消す前に:消えるものと安全な消し方

上のマクロは、必要な名前も区別せずに削除します。実際に5種類の名前を作ったブックで実行すると、次のようになりました。

名前の種類	削除後に起きたこと
数式で使っている名前(=単価*2)	数式が「#NAME?」エラーになった
入力規則のリストに使っている名前	規則に名前だけが残り、参照先が無くなった
印刷範囲(Print_Area)	印刷範囲の設定が消えた
参照先が「#REF!」の名前・非表示の名前	消えた(本来消したいもの)
テーブルの名前	対象外で、消えなかった

また、On Error Resume Nextがあるため、削除できなかった名前があっても、エラーが出ずに終わります。エラーを無視する書き方の注意点は、On Error Resume Nextの使い方と注意点で解説しています。

名前を活用しているブックでは、次の3つのマクロを順に使うと安全です。

①削除する前に、名前の一覧を書き出す

新しいシートに、名前・参照範囲・表示/非表示を書き出します。消した後に困ったとき、元に戻す手がかりになります。

Sub M_名前定義を一覧に書き出す()

    Dim 名前 As Name
    Dim 出力先 As Worksheet
    Dim 行 As Long

    Set 出力先 = ActiveWorkbook.Worksheets.Add
    出力先.Range("A1:C1").Value = Array("名前", "参照範囲", "表示")

    行 = 2
    For Each 名前 In ActiveWorkbook.Names
        出力先.Cells(行, 1).Value = 名前.Name
        出力先.Cells(行, 2).Value = "'" & 名前.RefersTo
        出力先.Cells(行, 3).Value = 名前.Visible
        行 = 行 + 1
    Next

End Sub

②参照先がエラー(#REF!)の名前だけを削除する

参照先のシートが消えた名前は、参照範囲が「=#REF!$A$1」のようになります。この「#REF!」を含む名前だけを削除します。

Sub M_エラーの名前定義だけを削除()

    Dim i As Long
    Dim 名前 As Name
    Dim 件数 As Long

    '// 削除すると番号がずれるため、後ろから順に調べる
    For i = ActiveWorkbook.Names.Count To 1 Step -1
        Set 名前 = ActiveWorkbook.Names(i)
        If InStr(名前.RefersTo, "#REF!") > 0 Then
            名前.Delete
            件数 = 件数 + 1
        End If
    Next i

    MsgBox 件数 & "件の名前を削除しました。"

End Sub

③非表示の名前を表示する

非表示の名前を表示に切り替えると、「名前の管理」で中身を確かめてから、手作業で削除できます。

Sub M_非表示の名前定義を表示する()

    Dim 名前 As Name

    For Each 名前 In ActiveWorkbook.Names
        名前.Visible = True
    Next

End Sub

(参考:Microsoft Learn「Name.Visible プロパティ」)

3つの削除方法の比較

名前を使っていないブックなら全削除、使っているブックならエラーの名前だけを消すのが基本です。

方法	向いている場面	注意点
「名前の管理」で手作業	数十件まで。中身を見ながら消したい	非表示の名前は出てこない
全削除のマクロ	名前を一切使っていないブック	数式・入力規則・印刷範囲も壊れる
エラーの名前だけ削除するマクロ	名前を活用しているブック	参照先が生きている不要な名前は残る

名前の定義を削除するときの注意点

• 先にブックを保存しておく:マクロで行った削除は、Ctrl+Zで元に戻せません。コピーしたブックで試すと安心です
• 印刷範囲・印刷タイトルも名前:Print_Area・Print_Titlesも名前の一つです。消すと印刷の設定がやり直しになります
• Excelが自動で作る名前:「_xlfn.」や「_FilterDatabase」で始まる名前は、Excelが内部で使うものです。無理に消さず、そのままにします
• テーブルの名前:テーブルの名前は、名前の削除では消えません。不要なら、テーブルを「範囲に変換」します

よくある質問

Q. 「名前の管理」に出てこない名前があるのはなぜ?

非表示(Visible = False)に設定された名前だからです。③のマクロで表示に切り替えると、「名前の管理」に出てきます。

Q. シートをコピーするたびに「名前は既に存在します」と聞かれます

コピー元とコピー先に同じ名前があるときに出るメッセージです。多くは、使われていない古い名前が原因です。
一覧を書き出して不要な名前を確かめ、エラーの名前や非表示の名前を整理すると出なくなります。

Q. 名前を消しても、ブックが軽くなりません

名前の定義以外の残骸が原因かもしれません。よくあるのは、増えすぎたセルのスタイルと、過去に使ったまま残っているセル範囲です。
それぞれ、不要なスタイルを一括削除する方法と、使用済みのワークシートをクリアする方法で解説しています。

まとめ|名前を使っているブックは、エラーの名前だけを消す

今回のテーマは、「名前の定義を一括削除」について、解説を行いました。

• 数十件までなら「名前の管理」で「エラーのある名前」に絞って削除する
• 全削除のマクロは、数式・入力規則・印刷範囲の名前まで消すので、名前を使っていないブックだけに使う
• 削除の前に保存し、一覧を書き出してから、エラーの名前だけを消す

本記事が、Excel実務の効率化において、ご参考お役立てになれば幸いです。

---

Excel業務改善事例ショート動画

文章だけでは伝わりにくい改善事例を、ショート動画でご覧いただけます。

◆よくある質問　◆Excel業務改善の実例　◆VBA・マクロ活用事例