Hi, all right, welcome back to the Mr. Excel Netcast, I am Bill John. So here is a question send in by Mike from Oklahoma City. Mike has a workbook where he says he has a whole bunch of different defined that names and most of those names are now not in use or not in there resolving their reference ever since. They did not like to get rid of all of them.
Something short of going into insert name, to fun choosing the name and clicking Delete and going through again and again and again. So an easy way to do this is just a short little three line Macro. I want to press Alt+F11 to get to the Visual Basic Editor, then we will go to Insert Module so let us call it DEL names and here are the three lines for each NM that is a variable in active workbook.names.
Nm.delete; delete is the method then actually will delete and then in the next nm. Basically, we could run that code until we go through and delete every single define name in the workbook in a second. Now I bet though that we do not really want to delete all of the names just for the ones that are evaluating to reference errors. So, I am not sure how to do that but we can learn.
I am going to press F8 to run this Macro one line at a time and once I get into NM, I am going to right-click and choose Add watch. Here is what we get in the watch window, it basically shows us one line but you want to click the plus sign and now we could see all of the properties of that verbal called NM. And I see that there is a property called refers to and you will see here that this particular one happens refer to pound ref exclamation point at the end.
And if I run this a few more times, you will see that we will get and a true address when it is valid. So if I look at that right most four characters of the first two property, I can probably customize this Macro a little bit, so let me try it here. We will say “If the right of nm.refersto,4 = ref!, then we will do the delete and if. So this way we are only going to delete the bad names from the list then we will run it.
It just runs in less than a second and now if we go back to Insert Name Define, we should only be left with names that are valid and none of these will be reference errors. So, a cool way to very quickly delete either all names or just the invalid names from your workbook rather than doing it one at a time.
Again, tiny little Macro, either three lines or five lines but it will certainly make this process which would have been painful very, very quick. I want to thank Mike for sending it in and I want to thank you for stopping by and we will see you next time for another Netcast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services