Harold from Tulsa asked how to write a macro to simulate double-clicking the fill handle. Episode 1152 shows you how.
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.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question came during a seminar last week in Oklahoma City.
You know, I always show how to shoot a formula down, right, we double-click the fill handle, and it figures out how many rows we have in the next column, copies it down.
And someone stopped me right there and said “Hey wait, I need to record a macro that'll do that.” And sure enough, when you turn on the macro recorder and do this, it hard codes that it copied down to row 21.
So tomorrow, if you have more rows or less rows, it is not going to work.
Let me play that macro back here, hang on, Alt+F8, and see, didn't go far enough.
Alright, so very frustrating, I said “Alright, I bet there's a way that, not using the macro recorder, that we could actually figure this out.” And the macro is going to take advantage of something called the Current Region.
Current Region is what you get when you are in a cell and you hit Ctrl+*, alright, it extends that section out ‘till it hits blank rows or the edge of the spreadsheet, all the way around.
And so conceptually, I'm going to have a macro look at the current region, and figure out how many rows there are, figure out what row I'm in, and then figure out how far down to copy.
So let's take a look at that macro, so it's a 3 or a 4 line macro, this is the recorded one, you can see they hard-coded things there, let's go over to the one that I wrote here.
Alright so first thing, a couple of variables, three variables I'm going to use.
First one called RowCount from the ActiveCell, hit the CurrentRegion, in other words, Shift+*, figure out how many rows you have, and count those.
Next up FinalRow, take the ActiveCell.CurrentRegion and from the collection of rows go to the very last one and see what row it's on.
So in this case, that would tell me that I'm down in row 24, and then to figure out how many RowsToCopy, take that 24 minus the current row, which in this case is 4, and +1.
So that's going to give me rows to copy, and then once I have that, once I know how far I want to copy, take the active cell and copy it.
The destination is going to be from the ActiveCell.Resize(RowsToCopy,1), so CopyDown, alright.
Now, let's give this a try, first of all, we're going to assign a shortcut key to that.
So I click Macros, CopyDown, Options, oh geez, I don't know what to do, let's do Ctrl+Shift+A, so I press Shift+A in there, click OK, click Cancel to close.
And we have our formula here, Ctrl+Shift+A, and BAM, it copies it right down.
I will try that again with lesser number of rows and make sure that it works, put a new formula out here, =2*that, and Ctrl+Shift+A, and there we go.
And so, you know, I don't know what's faster than double-clicking the fill handle, but he wanted a nice shortcut key that people could do, conceptually, what double-click the fill handle does, and a great way to go, a 4-line macro here.
Certainly, you'll never get this with a macro recorder, but easy enough to add this to your personal macro workbook and have it available all the time.
All right hey, well tomorrow's Thanksgiving here in the United States, so it'll be no podcast tomorrow, we'll be back on Friday.
And don't forget, as you're going out shopping on Friday, the big start of the retail Christmas season.
You go to the MrExcel store, a coupon code of Turkey20, save 20% off on your order from the MrExcel store on Friday!
All right well, hey, have a great long weekend for those of you don't have to work, we'll get something to you on Friday, rest of you will see on Monday.
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today's question came during a seminar last week in Oklahoma City.
You know, I always show how to shoot a formula down, right, we double-click the fill handle, and it figures out how many rows we have in the next column, copies it down.
And someone stopped me right there and said “Hey wait, I need to record a macro that'll do that.” And sure enough, when you turn on the macro recorder and do this, it hard codes that it copied down to row 21.
So tomorrow, if you have more rows or less rows, it is not going to work.
Let me play that macro back here, hang on, Alt+F8, and see, didn't go far enough.
Alright, so very frustrating, I said “Alright, I bet there's a way that, not using the macro recorder, that we could actually figure this out.” And the macro is going to take advantage of something called the Current Region.
Current Region is what you get when you are in a cell and you hit Ctrl+*, alright, it extends that section out ‘till it hits blank rows or the edge of the spreadsheet, all the way around.
And so conceptually, I'm going to have a macro look at the current region, and figure out how many rows there are, figure out what row I'm in, and then figure out how far down to copy.
So let's take a look at that macro, so it's a 3 or a 4 line macro, this is the recorded one, you can see they hard-coded things there, let's go over to the one that I wrote here.
Alright so first thing, a couple of variables, three variables I'm going to use.
First one called RowCount from the ActiveCell, hit the CurrentRegion, in other words, Shift+*, figure out how many rows you have, and count those.
Next up FinalRow, take the ActiveCell.CurrentRegion and from the collection of rows go to the very last one and see what row it's on.
So in this case, that would tell me that I'm down in row 24, and then to figure out how many RowsToCopy, take that 24 minus the current row, which in this case is 4, and +1.
So that's going to give me rows to copy, and then once I have that, once I know how far I want to copy, take the active cell and copy it.
The destination is going to be from the ActiveCell.Resize(RowsToCopy,1), so CopyDown, alright.
Now, let's give this a try, first of all, we're going to assign a shortcut key to that.
So I click Macros, CopyDown, Options, oh geez, I don't know what to do, let's do Ctrl+Shift+A, so I press Shift+A in there, click OK, click Cancel to close.
And we have our formula here, Ctrl+Shift+A, and BAM, it copies it right down.
I will try that again with lesser number of rows and make sure that it works, put a new formula out here, =2*that, and Ctrl+Shift+A, and there we go.
And so, you know, I don't know what's faster than double-clicking the fill handle, but he wanted a nice shortcut key that people could do, conceptually, what double-click the fill handle does, and a great way to go, a 4-line macro here.
Certainly, you'll never get this with a macro recorder, but easy enough to add this to your personal macro workbook and have it available all the time.
All right hey, well tomorrow's Thanksgiving here in the United States, so it'll be no podcast tomorrow, we'll be back on Friday.
And don't forget, as you're going out shopping on Friday, the big start of the retail Christmas season.
You go to the MrExcel store, a coupon code of Turkey20, save 20% off on your order from the MrExcel store on Friday!
All right well, hey, have a great long weekend for those of you don't have to work, we'll get something to you on Friday, rest of you will see on Monday.
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
