Sometimes I inherit workbooks with hidden range names that cause the same
problems.

Drop this code into a General Module, then go to any sheet and run the
ListActShtRngNames macro:

Sub ListActShtRngNames()
Dim strText As String
Dim intNameCount As Integer
Dim intCtr As Integer
Dim blnMakeVisible As Boolean

strText = ""

Select Case MsgBox( _
Title:="Hidden Range Name Option", _
Prompt:="Do you want to force all hidden range names to be made
visible?", _
Buttons:=vbCritical + vbYesNoCancel)

Case vbYes:
blnMakeVisible = True
Case vbNo:
blnMakeVisible = False
Case vbCancel
Exit Sub
End Select

With ActiveWorkbook
intNameCount = .Names.Count
For intCtr = 1 To intNameCount
strText = strText & Names(intCtr).Name & " " _
& Names(intCtr).Visible & " " _
& Names(intCtr).RefersTo & vbCr

If Names(intCtr).Visible = False Then
Names(intCtr).Visible = blnMakeVisible
End If

Next intCtr

End With
MsgBox strText

End Sub

Note: you may need to run it on each sheet if there are Sheet level names.

I hope that helps.
--
Regards,
Ron