Mike from OK City asks how to delete all names in a workbook. In Episode 904, I will show you a way to delete all names or to delete only the invalid names.
This video podcast is the companion to the book, Learn Excel 97-2007 from MrExcel.
This video podcast is the companion to the book, Learn Excel 97-2007 from MrExcel.
Transcript of the video:
Hey, alright, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question sent by Mike from Oklahoma City.
Mike has a workbook where he says he has a whole bunch of different defined names, and most of those names are now not in use-- they're resolving to reference errors.
He said he'd like to get rid of all of them; something short of going into Insert, Name, Define, 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 over to the Visual Basic editor, and we'll go to Insert; Module; Sub-- let's call it-- DelNames()-- and here's the three lines-- For each nm-- that's a variable-- in activeworkbook.names nm.delete-- delete is the method that actually will delete that, and then say-- next nm.
Basically, we can run that code, it would go through and delete every single defined name in the workbook in a second.
Now, I bet though, that we don't really want to delete all the names-- just the ones that are evaluating to reference errors.
So I'm not sure how to do that, but we can learn.
I'm going to press F8 to run this macro one line at a time, and once I get into "nm", I'm going to right-click and choose Add Watch.
Here's what we get in the watch window: It basically shows us one line, but you want to click the plus sign and now we can see all the properties of that variable called "nm".
And I see that there's a property called RefersTo, and you'll see here that this particular one happens refer to "#REF!" If I run this a few more times you'll see that we will get a true address when it's valid.
So if I look at that rightmost four characters of the RefersTo property, I can probably customize this macro a little bit.
So let me try it here.
We'll say: If right(nm.refersto, 4) = "REF!" then nm.Delete End If.
So this way, we're only going to delete the bad names from the list.
We'll 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 certainly makes this process-- which would have been painful-- very, very quick.
Want to thank Mike for sending that in, want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today's question sent by Mike from Oklahoma City.
Mike has a workbook where he says he has a whole bunch of different defined names, and most of those names are now not in use-- they're resolving to reference errors.
He said he'd like to get rid of all of them; something short of going into Insert, Name, Define, 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 over to the Visual Basic editor, and we'll go to Insert; Module; Sub-- let's call it-- DelNames()-- and here's the three lines-- For each nm-- that's a variable-- in activeworkbook.names nm.delete-- delete is the method that actually will delete that, and then say-- next nm.
Basically, we can run that code, it would go through and delete every single defined name in the workbook in a second.
Now, I bet though, that we don't really want to delete all the names-- just the ones that are evaluating to reference errors.
So I'm not sure how to do that, but we can learn.
I'm going to press F8 to run this macro one line at a time, and once I get into "nm", I'm going to right-click and choose Add Watch.
Here's what we get in the watch window: It basically shows us one line, but you want to click the plus sign and now we can see all the properties of that variable called "nm".
And I see that there's a property called RefersTo, and you'll see here that this particular one happens refer to "#REF!" If I run this a few more times you'll see that we will get a true address when it's valid.
So if I look at that rightmost four characters of the RefersTo property, I can probably customize this macro a little bit.
So let me try it here.
We'll say: If right(nm.refersto, 4) = "REF!" then nm.Delete End If.
So this way, we're only going to delete the bad names from the list.
We'll 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 certainly makes this process-- which would have been painful-- very, very quick.
Want to thank Mike for sending that in, want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
