Written by:

Written by:

Thomas Szigeti

Another one of those issues that you come across when you are busy, and have to do things on the fly.
When your manager sends you a file to adjust something.
Then you open the file, and start to get the feeling that “something is not right here”…

And you start to look around in the Excel workbook structure and open something like this:

Click on the thumbnail on the right to see what you could be facing.

I tried to make it as painless for you as I can by putting it in to a small image.

This is a very good example of what could come through the door of the everyday Analyst or Admin.

I was looking at this list and it was obvious that these named ranges were made by someone selecting a table and hit the button “Create Named Range From Selection”. Well… Here knowing this will not help us much. 
And now you might be thinking. Thanks but no thanks. I will just leave it as it is. 🙂
Unfortunately my OCD kicked in and I had to do something…

First things first.

AKA How do I know if it is safe to delete the named ranges with #errors in it?

 

The short answer: We don’t. 

But we can assume that any named range with a #REF error means that part of the Named Range that the formula which describes it is missing. (Means it has been deleted from the workbook)
Because of the deletion and the #REF error this named range will not work.

I asked the current user of the file if he knows anything about the named ranges?

He let out a quiet scream and said that he never seen them before.
O.K. So I WILL delete them. 
All of them with errors.

 

How would I go about deleting a LOT of named ranges?

We could go and delete them one by one, but that is not our style. 

A better solution would be to Shift click and highlight a bunch of them. Then delete a few rows at a time in the Name Manager.

A good excercise would be to code this in VBA. -Right? 😉

Sub DeleteBadNamedRanges()

    Dim BadName As Name
    
    For Each BadName In ActiveWorkbook.Names

        If InStr(BadName.Value, "#REF!") > 0 Then

            BadName.Delete

        End If

    Next BadName

End Sub

Code Explanation:

It is a simple For Next Loop that goes through the Named Range collection. 

In the middle part there is an if that checks the Named range’s value with the InStr (In string) function to see if the “#REF” error is in present. 

If the error characters are found it will delete the named range. 

So on, so forth…

Come and visit us.

Come and visit excelangel on Facebook to find and join the growing community of true office workers.

Oh, I almost forgot.
Drop us a like while you there...

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]