Excelでシートコピーすると名前重複のダイアログが出て…というよくある話
表示されていない（非表示の）名前定義を表示するところから始まり、
参照エラーの名前定義など条件付きで削除するマクロを作成・使用してみましたが、
結局ほぼすべて削除だなというところに至りました。
せっかく作ったので、途中で作った条件付き削除のマクロなども載せておきます。
名前定義の表示・非表示
2 → 3 → 1 の順で作成。
1. FilterDatabase以外を表示
FilterDatabaseは削除するとフィルタが壊れるので、非表示のまま
Public Sub showNames()
'
' 非表示の名前定義を表示する
' FilterDatabaseは表示しない
'
Dim nm As name
On Error Resume Next
For Each nm In Names
If nm.name Like "*!_FilterDatabase" Then
' FilterDatabaseは表示しない
ElseIf nm.Visible = False Then
nm.Visible = True
End If
Next
On Error GoTo 0
MsgBox "完了", vbOKOnly
End Sub
2. 全て表示
非表示になっているものをとりあえず全部表示
Public Sub showAllNames()
'
' 非表示の名前定義を表示する
'
' 参考 https://dev.classmethod.jp/tool/excel-delete-name/
'
Dim name As Object
For Each name In Names
If name.Visible = False Then
name.Visible = True
End If
Next
MsgBox "完了", vbOKOnly
End Sub
3. FilterDatabaseを非表示
（フィルタを壊して）やっぱりFilterDatabaseは非表示に
Public Sub hideNameFilterDB()
'
' 名前定義FilterDatabaseを非表示にする
'
Dim nm As name
On Error Resume Next
For Each nm In Names
If nm.name Like "*!_FilterDatabase" Then
nm.Visible = False
End If
Next
On Error GoTo 0
MsgBox "完了", vbOKOnly
End Sub
名前定義の削除
除外対象以外の名前定義を削除
除外対象
- フィルタ関連（FilterDatabase）
- 印刷設定関連（Print_Area、Print_Titles）
Public Sub delNames()
'
' 名前定義を削除する
' 以下の除隊対象は削除しない
' * FilterDatabase
' * Print_Area
' * Print_Titles
'
Dim nm As name
On Error Resume Next
For Each nm In Names
If nm.name Like "*!_FilterDatabase" _
Or nm.name Like "*!Print_Area" _
Or nm.name Like "*!Print_Titles" Then
' 除外対象の場合は処理しない
Else
nm.Delete
End If
Next
On Error GoTo 0
MsgBox "完了", vbOKOnly
End Sub
テーブル名は名前定義ではない
Excelの名前の管理ではテーブル名も含めて表示されますが、
テーブル名は名前定義 Names には含まれません。
これを実行するとテーブルも削除されるのではと心配していましたが、削除されません。
エラーの名前定義を削除
RefersTo （参照先の文字列）に、#REF! か #N/A が含まれる名前定義を削除
（これも）2 → 3 → 1 の順で作成。
- エラー削除1
Public Sub delErrorNames()
'
' エラーの名前定義を削除
'
Dim nm As name
On Error Resume Next
For Each nm In names
If nm.RefersTo Like "*[#]REF!*" _
Or nm.RefersTo Like "*[#]REF'!*" _
Or nm.RefersTo Like "*[#]N/A*" Then
nm.Delete
End If
Next
On Error GoTo 0
MsgBox "完了", vbOKOnly
End Sub
エラー削除2の条件 nm.RefersTo Like "*[#]REF!*" では
'C:\file path\[book.xls]#REF'!$A$1 というような参照範囲が対象にならなかったため、
nm.RefersTo Like "*[#]REF'!*" を削除するパターンに追加。
- エラー削除2
Public Sub delErrorNamesREF()
'
' エラーの名前定義を削除
'
' 参考 https://excel-ubara.com/excelvba5/EXCELVBA214.html
'
Dim nm As name
On Error Resume Next
For Each nm In Names
If nm.RefersTo Like "*[#]REF!*" Then
nm.Delete
End If
Next
On Error GoTo 0
MsgBox "完了", vbOKOnly
End Sub
- エラー削除3
Public Sub delErrorsNamesNA()
'
' エラーの名前定義を削除
'
Dim nm As name
On Error Resume Next
For Each nm In Names
If nm.RefersTo Like "*[#]N/A*" Then
nm.Delete
End If
Next
On Error GoTo 0
MsgBox "完了", vbOKOnly
End Sub
別ブック参照の名前定義を削除
RefersTo （参照先の文字列）に、.xls と ! が含まれる名前定義を削除
自ブックに、.xls を含むシート名があり、そのシート内の範囲を定義していると
それも削除対象になると思います。
Public Sub delOtherBookNames()
'
' 別のExcelを参照している名前定義を削除
'
Dim nm As name
On Error Resume Next
For Each nm In Names
If nm.RefersTo Like "*.[xX][lL][sS]*!*" Then
nm.Delete
End If
Next
On Error GoTo 0
MsgBox "完了", vbOKOnly
End Sub
表示されている名前定義を削除
作ってみたけどあまり使うことはなさそうだなとなったやつ。
表示されているものは名前の管理から削除できるので…。
参考に残しておきます。
Public Sub delVisibleNames()
'
' 表示されている名前定義を削除する
'
Dim nm As name
On Error Resume Next
For Each nm In Names
If nm.Visible = True Then
nm.Delete
End If
Next
MsgBox "完了", vbOKOnly
End Sub
参考ページ
（一部のソース内にも入ってますが）作成時に参考にしたページ