Delete Broken Named Ranges in Excel Using VBA

Question

Part of a much larger macro involves the detection and deletion of Named Ranges that were unintentionally duplicated upon the Move/Copy of a sheet to the main workbook. These "broken" Named Ranges are indicated by the "Value" column in the Name Manager showing "#REF!".

I've attempted the deletion of these named ranges using the following macro:

Sub DeleteBrokenNamedRanges()
Dim NR As Name
Dim numberDeleted As Variant

numberDeleted = 0
For Each NR In ActiveWorkbook.Names
    If InStr(NR.Value, "#REF!") > 0 Then
        NR.Delete
        numberDeleted = numberDeleted + 1
    End If
Next

MsgBox ("A total of " & numberDeleted & " broken Named Ranges deleted!")

End Sub

Unfortunately, the return value is 0 and no Named Ranges were deleted. I played around with protect/unprotect and some parameters of InStr, but nothing has worked.

Side Note - The return of NR.Value is not #REF! or similar error code as expected, but is actually the =C:\Blahblah\blarg.xls path instead.

Any help on this would be greatly appreciated, thanks!

it works perfectly fine for me, saying 7 broken ranges were deleted from a test file. Maybe checking the cell value for the #REF might work. — ballsy26
– ballsy26, Commented Dec 5, 2016 at 22:07
Whhaaatt. I'm running Excel 2013, could that be an issue? I saw that InStr has had a past change, but can't tell if that's related. The #REF! is due to the path to the file containing the subject named range erroring, because the file doesn't exist. — K.C. Grimes
– K.C. Grimes, Commented Dec 5, 2016 at 22:16
I'm running excel 2010, as a test try creating a new range and then deleting the entire row so create a #REF error and then run your code. If it works then you'll know it's got something to do with the file path error. — ballsy26
– ballsy26, Commented Dec 5, 2016 at 22:18
I agree in that it is due to the pathing error, because it's not returning #REF! as a value but instead the path. My workaround is looking for ".xls" instead of "#REF!" because this would indicate the use of an external sheet, which, in this specific case, actually contains all of the needed references. It's not very dynamic, but for my purposes it is fine. A true fix would still be nice however. — K.C. Grimes
– K.C. Grimes, Commented Dec 5, 2016 at 22:24

Wedge · Accepted Answer · 2016-12-06 00:36:41Z

3

I don't think error codes exist as range values (not that I'm any authority on this). If valid ranges had errors other than #REF! it could be a problem, but I think something like

IsError(NR.RefersToRange)

Might be a good way to check for broken named ranges?

answered Dec 6, 2016 at 0:36

Wedge

1,8561 gold badge11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete Broken Named Ranges in Excel Using VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related