I’ve recently added a new macro to my Personal.xls (that’s four now!). This one is to replace the cumbersome Edit > Fill > Series > Autofill (alt-e-i-s, alt-f, enter).
If TypeName(Selection) = “Range” Then
Selection.DataSeries , xlAutoFill
This uses the DataSeries method of the Range object. Honestly, I expected there to be a FillAuto method, but it turns out it’s called AutoFill. That really is a better name, so I don’t know what I was thinking. I was probably thinking along the lines of FillDown and FillRight. I’m not sure the AutoFill method would work in this capacity, however, because I wouldn’t know on which Range to perform the AutoFill. For instance, if I had the numbers 1 through 7 in A1:A7, I select A1:A10 and run the above macro to fill the series down to get 1 through 10. The equivalent AutoFill would look like
I can replace Range(“A1:A10?) with Selection, but I don’t know how to replace Range(“A1:A7?). Maybe that’s why they have two methods for this. It works for me, and that’s what’s important. Here’s a rundown of the arguments for the DataSeries method:
Rowcol: You can specify whether to fill by rows (xlRows) or columns (xlColumns). I’ve never changed Excel’s guess in the user interface, so I didn’t see the need to include my own logic in this macro. I omit the argument and take Excel’s guess.
Type: This corresponds to the four option buttons on the Fill Series Dialog; Linear, Growth, Date, AutoFill. Linear is the default, which was surprising to me. I thought AutoFill would be. I’m not sure I understand what these mean, but my best guess is that AutoFill determines the proper type of fill based on the data that’s already selected. That works for me most of the time.
And the rest: The Professor and Mary Ann of the DataSeries arguments. You can determine how to increment the series, when to stop it, and whether to create a trend. I always enter the first two cells which determines the increment, I stop selecting when I want the series to stop, and I leave the trend setting to Old Navy.