Excelでシートコピーすると名前重複のダイアログが出て…というよくある話
表示されていない（非表示の）名前定義を表示するところから始まり、
参照エラーの名前定義など条件付きで削除するマクロを作成・使用してみましたが、
結局ほぼすべて削除だなというところに至りました。
せっかく作ったので、途中で作った条件付き削除のマクロなども載せておきます。
名前定義の表示・非表示
2 → 3 → 1 の順で作成。
1. FilterDatabase以外を表示
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は非表示に
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以外を削除
FilterDatabase以外を削除
Public Sub delNames()
'
' 名前定義を削除する
' FilterDatabaseは削除しない
'
Dim nm As name
On Error Resume Next
For Each nm In Names
If nm.name Like "*!_FilterDatabase" Then
' FilterDatabaseは処理しない
Else
nm.Delete
End If
Next
On Error GoTo 0
MsgBox "完了", vbOKOnly
End Sub
テーブル名は名前定義ではない
Excelの名前の管理ではテーブル名も含めて表示されますが、
テーブル名は名前定義 Names には含まれません。
これを実行するとテーブルも削除されるのではと心配していましたが、削除されません。
エラーの名前定義を削除
RefersTo （参照先の文字列）に、#REF! か #N/A が含まれる名前定義を削除
（これも）2 → 3 → 1 の順で作成。
エラー削除1：#REF! または #N/A
Public Sub delErrorNames()
'
' エラーの名前定義を削除
'
Dim nm As name
On Error Resume Next
For Each nm In Names
If nm.RefersTo Like "*[#]REF!*" Then
nm.Delete
Else
If nm.RefersTo Like "*[#]N/A*" Then
nm.Delete
End If
End If
Next
On Error GoTo 0
MsgBox "完了", vbOKOnly
End Sub
エラー削除2：#REF!
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：#N/A
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
参考ページ
（一部のソース内にも入ってますが）作成時に参考にしたページ