Deleting Broken Defined Name

I've been using the following code to get the the number of defined name ranged down to a manageable list (e.g., being able to open name range manager):

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

End Sub

In a recent hand me down file with a significant amount of stalled name ranged, I get an "out of memory" (7) error. Any recommendations on code alterations to make it run?

Thanks