Paul uses the macro recorder to record the action of naming the current region. The macro recorder never gets this correct. There is a far simpler version of code to create named ranges. Episode 676 will show you how to replace the recorded code.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question that's sent in by Paul. Paul from Derby.
Now you remember Paul.
On Friday, he gave us the great idea about how to do VLOOKUP using a named range and he sent in a question with his suggestion.
He said anytime that I use the macro recorder to record a named range, it's not doing what I want.
So basically Paul said. You know, try this go to "Tools" "Macro" "Record New Macro" and I'll call it aaaNameRange Will show up at the top of the list.
Click OK Paul says first thing I do is I select the entire current region So that's control+& to select the current region and then I define a name, so maybe "Mydata" Click OK.
All right now. We'll stop recording and I'm going to delete that define name. "Insert" "Name" "Define", and I'll delete that.
Click OK but now here's the problem.
Tomorrow Paul's data might have a larger area. All right, so we select one cell in the data. We run the macro Run and while it selects all of the data, it did not name it correctly. Unfortunately the macro recorder hard-Coded that the named range is always going to be that original area.
Which is very very frustrating.
Now Let's go take a look at this code.
"Edit" and basically what it's doing is, it's doing CurrentRegion.Select at that point.
I just want to say Selection.Name ="MyData" Far easier Syntax than whatever the Macro recorder does.
We don't have to worry about the refers to, variable or anything like that, it just simply works now.
Let's come back, and we'll test again. So this time let me delete a few cells here and I'll delete the original definition of "MyData" "Delete" Click ok All right select one cell will run the macro.
It selects all the data and already I can tell it work because the name box is showing the correct range if I would reselect and ask for my data, it has now named the entire current region.
So you know there's a few things that you have to learn and the macro recorder just don't work.
Naming ranges, it is not necessarily the best thing in the macro recorder.
It always hard codes the range that you have today by using this trick and basically changing one line of code and actually simplifying the line of code quite a bit.
Selection.Name = and then in quotes the name of the range.
It will solve that problem.
So thanks to Paul for sending in that great question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today we have a question that's sent in by Paul. Paul from Derby.
Now you remember Paul.
On Friday, he gave us the great idea about how to do VLOOKUP using a named range and he sent in a question with his suggestion.
He said anytime that I use the macro recorder to record a named range, it's not doing what I want.
So basically Paul said. You know, try this go to "Tools" "Macro" "Record New Macro" and I'll call it aaaNameRange Will show up at the top of the list.
Click OK Paul says first thing I do is I select the entire current region So that's control+& to select the current region and then I define a name, so maybe "Mydata" Click OK.
All right now. We'll stop recording and I'm going to delete that define name. "Insert" "Name" "Define", and I'll delete that.
Click OK but now here's the problem.
Tomorrow Paul's data might have a larger area. All right, so we select one cell in the data. We run the macro Run and while it selects all of the data, it did not name it correctly. Unfortunately the macro recorder hard-Coded that the named range is always going to be that original area.
Which is very very frustrating.
Now Let's go take a look at this code.
"Edit" and basically what it's doing is, it's doing CurrentRegion.Select at that point.
I just want to say Selection.Name ="MyData" Far easier Syntax than whatever the Macro recorder does.
We don't have to worry about the refers to, variable or anything like that, it just simply works now.
Let's come back, and we'll test again. So this time let me delete a few cells here and I'll delete the original definition of "MyData" "Delete" Click ok All right select one cell will run the macro.
It selects all the data and already I can tell it work because the name box is showing the correct range if I would reselect and ask for my data, it has now named the entire current region.
So you know there's a few things that you have to learn and the macro recorder just don't work.
Naming ranges, it is not necessarily the best thing in the macro recorder.
It always hard codes the range that you have today by using this trick and basically changing one line of code and actually simplifying the line of code quite a bit.
Selection.Name = and then in quotes the name of the range.
It will solve that problem.
So thanks to Paul for sending in that great question and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
