Deleting Broken Defined Name

I have large model that over the years accumulated a lot of name ranges, to the point that the name manager will not open.

Most of the solutions that I found so far are not working to get the numbers of defined names down. I was hoping to run this code; however, I run into a memory error:

Sub DeleteNamedRangesWithREF()
    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
        If InStr(nm.Value, "#REF!") > 0 Then
            nm.Delete
        End If
    Next nm
End Sub

I also don't fully know which name ranges are needed versus not (or least by defined name) so a total delete of the list or a specific, delete anything that contains x,y,z would be difficult so ideally would want to stick with the delete any errors and hope name manager opens by then. None the less open to other suggestions.

Thanks