Today Bill shows us how to use the Macro Recorder to record the action of Double-clicking the Fill Handle. It isn't as straight-forward as you might think, but it can be done with only the macro recorder. Follow along with Bill in Episode #1519 to learn how.
...Today's Podcast topic may be found in Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions.
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...Today's Podcast topic may be found in Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions.
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Excel-XL.
Learn MrExcel podcast, Episode 1519; Record Fill Down.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question, you know one of my favorite features, is to double click the fill handle, auto copy the formula but that, you if you turn the Macro Recorder and try to record that action, it does not work.
All right so, let's record a Macro here and we'll just call this TryOne, Shortcut key of Ctrl+Shift+A, stored in this workbook.
All right so, here's what the macro does?
First, we go to View, Macros, use Relative References, come over here, New Head.
Go down one and our formula, I don't care what the formula is this?
This divide by that.
And then double click the fill handle.
All right so, we have our Macro, we wanna stop recording.
So, View, Macros, Stop Recording.
All right now, what's interesting is, if we try this Macro on the exact same data set, Ctrl+Shift+A, it works great.
The problem is, when we have more records, like this, they didn't actually record.
Ctrl+Shift+A, the action of double clicking the fill here, it only recorded, copying down to have remaining records, we had in the first day.
So, this is completely useless but I figured there had to be some way, that we could use the Macro Recorder to solve this problem.
So, let's try this second approach Macros, Record Macro, I'll call it, TryTwo, Ctrl+Shift+S, click OK Now, first thing I'm gonna do, I'm going to turn off the Relative Reference initially because there's two cells, I know that I wanna deal with.
I want to deal with this new Heading here and I want to deal with my formula equal this / that.
All right, that's interesting.
I always know that the first place for the formula is going to be in cell F2, every single time.
All right now, from here I'll go back to A1, at this point, that's when I turn on Relative Reference.
All right now, watch these steps, Ctrl down arrow to get me to the last row.
Ctrl right arrow to get me to the last column.
One more right arrow to get me to the bottom of the column, where I wanna copy things.
Then from here Ctrl+Shift up arrow, now this is really cool.
So now, I've selected that first formula that I entered in the Macro, plus all of the blank cells, all the way down.
Now, I talked about to double click the fill handle trick, all the time but there's another trick that would work right here, it's called Ctrl+ D, which is a shortcut for filled down, watch this Ctrl+D.
All right so, it fills everything down.
Now, that is a lot more hassle than double clicking the fill handle.
Admittedly, but the huge advantage is when we record the Macro in that way, I'll stop recording and come out here with more records, let's put more records in.
And do Ctrl+Shift+S, Bam!
It works no matter how many records there are.
Even, if there's less records, let's try that.
Ctrl+Shift+S, it still works every time.
All right so, if you're trying to record a Macro and use that fill down feature, it is possible but you know, you have to kind of in Absolute mode, enter the Heading, enter the first formula and then use in Relative mode, Ctrl+down arrow, Ctrl+right arrow to get over there and then Ctrl+shift+up arrow to select all the blank cells, plus the Heading and then from there Ctrl+D.
All right well, hey I wanna thank you for stopping by.
By the way, you know for VBA, if you wanna get beyond the Macro Recorder...
Well, thank you for stopping by. See you next time for another netcast from MrExcel.
Learn MrExcel podcast, Episode 1519; Record Fill Down.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question, you know one of my favorite features, is to double click the fill handle, auto copy the formula but that, you if you turn the Macro Recorder and try to record that action, it does not work.
All right so, let's record a Macro here and we'll just call this TryOne, Shortcut key of Ctrl+Shift+A, stored in this workbook.
All right so, here's what the macro does?
First, we go to View, Macros, use Relative References, come over here, New Head.
Go down one and our formula, I don't care what the formula is this?
This divide by that.
And then double click the fill handle.
All right so, we have our Macro, we wanna stop recording.
So, View, Macros, Stop Recording.
All right now, what's interesting is, if we try this Macro on the exact same data set, Ctrl+Shift+A, it works great.
The problem is, when we have more records, like this, they didn't actually record.
Ctrl+Shift+A, the action of double clicking the fill here, it only recorded, copying down to have remaining records, we had in the first day.
So, this is completely useless but I figured there had to be some way, that we could use the Macro Recorder to solve this problem.
So, let's try this second approach Macros, Record Macro, I'll call it, TryTwo, Ctrl+Shift+S, click OK Now, first thing I'm gonna do, I'm going to turn off the Relative Reference initially because there's two cells, I know that I wanna deal with.
I want to deal with this new Heading here and I want to deal with my formula equal this / that.
All right, that's interesting.
I always know that the first place for the formula is going to be in cell F2, every single time.
All right now, from here I'll go back to A1, at this point, that's when I turn on Relative Reference.
All right now, watch these steps, Ctrl down arrow to get me to the last row.
Ctrl right arrow to get me to the last column.
One more right arrow to get me to the bottom of the column, where I wanna copy things.
Then from here Ctrl+Shift up arrow, now this is really cool.
So now, I've selected that first formula that I entered in the Macro, plus all of the blank cells, all the way down.
Now, I talked about to double click the fill handle trick, all the time but there's another trick that would work right here, it's called Ctrl+ D, which is a shortcut for filled down, watch this Ctrl+D.
All right so, it fills everything down.
Now, that is a lot more hassle than double clicking the fill handle.
Admittedly, but the huge advantage is when we record the Macro in that way, I'll stop recording and come out here with more records, let's put more records in.
And do Ctrl+Shift+S, Bam!
It works no matter how many records there are.
Even, if there's less records, let's try that.
Ctrl+Shift+S, it still works every time.
All right so, if you're trying to record a Macro and use that fill down feature, it is possible but you know, you have to kind of in Absolute mode, enter the Heading, enter the first formula and then use in Relative mode, Ctrl+down arrow, Ctrl+right arrow to get over there and then Ctrl+shift+up arrow to select all the blank cells, plus the Heading and then from there Ctrl+D.
All right well, hey I wanna thank you for stopping by.
By the way, you know for VBA, if you wanna get beyond the Macro Recorder...
Well, thank you for stopping by. See you next time for another netcast from MrExcel.
