mass edit of named ranges (refer to)

Hi all,

working through a current excel workbook - due to multiple iterations and updates it seems a lot of the named ranges are still referring to a previous model of the excel file.
Essentially the cell reference is the same in the current version so I need to find a way to change the "refer to" section to remove the old link and replace it with nothing, essentially deleting it.
I found a few macros but they change the name, not the "refer to"
Hopefully the pic helps to convey what I am trying to achieve - note this isn't my situation but showing you what I need to change
thanks!

[EDIT]: I have uploaded a sample excel file that I just made. similar to what I am experiencing but I have 100's!

Perhaps ...

Please Login or Register  to view this content.

It simply checks every named range for a ']' character. This delimits a workbook name from a range name and is used to split the definition into an array. The upper bound of the array is the sheet/Cell reference so that is written back to the RefersTo property (Adds back the =' at the start while doing so).

After re-reading your post, this "...change the "refer to" section to remove the old link and replace it with nothing, essentially deleting it..." is confusing me. You mean to delete the reference to the external workbook or the named range itself? Hopefully, the former...