AutoFill Macro

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).

Sub FillSeriesAutoFill()
    If TypeName(Selection) = “Range” Then
        Selection.DataSeries , xlAutoFill
    End If
End Sub

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

Range(“A1:A7”).AutoFill Range(“A1:A10”), xlFillSeries

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.

Posted in Uncategorized

7 thoughts on “AutoFill Macro

  1. Dick,

    Very useful.

    You probably already know this, but…
    If you have A1:A10 containing 123.
    then type A1 = 1, A2 = 2.
    select A1:A2 then double-click the fill handle, it fills down in the column region.

    Rob

  2. Dick,

    This macro will take the first two items and autofill down or across depending on the selection. Note that the selection must be in one row OR one column.

    Sub FillAuto()
    If Selection.Columns.Count = 1 And Selection.Cells.Count > 1 Then
    Selection.Range(Cells(1, 1), Cells(2,1)).AutoFill Selection
    ElseIf Selection.Rows.Count = 1 And Selection.Cells.Count > 1 Then
    Selection.Range(Cells(1, 1), Cells(1,2)).AutoFill Selection
    End If
    End Sub

    HTH…

    David

  3. I too added a fill handle macro to my personal (that makes 954) but I wanted it to work for blocks and even multiple areas. Of course I’ve never actually needed to do more than one column or row at a time but maybe someday.

    Jim

    “An attempt to do what dragging the Fill Handle does
    “To be attached to a keyboard shortcut (Ctrl-Shift-h) so
    “I do not have to drag the file handle which is awkward
    “for large ranges.
    “Works with multiple area selections, mixed columns
    ” and rows, treating each column and row separately
    Sub FillHandleSubst()
    Dim CurrArea As Range, StepVal As Double
    Dim CurrSlice As Range
    For Each CurrArea In Selection.Areas
    If RangeIsFilled(CurrArea.Columns(1)) Then
    “If entire first col is filled we’re filled across rows
    For Each CurrSlice In CurrArea.Rows “Do each row separately
    StepVal = GetStepVal(CurrSlice)
    CurrSlice.DataSeries Rowcol:=xlRows, Step:=StepVal
    Next
    Else
    “Since first col is not filled, fill down columns individually
    For Each CurrSlice In CurrArea.Columns
    StepVal = GetStepVal(CurrSlice)
    CurrSlice.DataSeries Rowcol:=xlColumns, Step:=StepVal
    Next
    End If
    Next
    End Sub

    “Returns True if entire passed range is filled
    Function RangeIsFilled(Rg As Range) As Boolean
    On Error Resume Next
    RangeIsFilled = (Application.CountA(Rg) = Rg.Cells.Count)
    End Function

    “The difference between the first and second cells in the
    ” passed row or column is the step amount.
    “But if the second cell is empty make the step 1.
    Function GetStepVal(Rg As Range) As Double
    Dim Cell2Val As Variant
    Cell2Val = Rg.Cells(2).Value
    If Cell2Val = “” Then
    GetStepVal = 1
    Else
    GetStepVal = Cell2Val – Rg.Cells(1)
    End If
    End Function

  4. hi
    i have data that changes regulary. it wrote a macro to import into excel. after it has been imported, not all cells in each column has data in it. what i need is to autofill the cell in a column until just before a change in field and then it must autofill the new field. until the next and so on.

    please assist.

  5. Dim jmp As Integer
    For i = 1 To 172
    jmp = 1
    If Not IsNumeric(Range(“A1?)) Then
    ‘MsgBox (“Value of Cell is Alpabetic” + Range(“A1?))
    Range(“&value(jmp) &:& value(jmp)&”).Select
    Range(“1:1?).Select
    Selection.Delete Shift:=xlUp
    ‘Rows(“9:9?).Select
    ‘Selection.Delete Shift:=xlUp
    Else
    jmp = jmp + 1
    End If
    Next
    End Sub

    Can i use some varibles in place of the varible JMP.

  6. Hello Members,

    I have a question for how to create a macro in Excel for below problem.

    I have a sequence of data in Excel below mentioned and I want to autofill the the C and D columns with the similar C1 and D1 Autofill down to C18 and D18 then start selection again from C19 and D19 to C36 and D36 Autofill and goes on like this till Last Rows 65534.

    Would Appreciate if anyone can help me in this.

    Arshad

    A B C D
    1 1632497 3629842409838
    2 3742803
    3 4972902
    4 6353011
    5 7303046
    6 8563079
    7 9633087
    8 11233091
    9 13733398
    10 16383729
    11 18583990
    12 20834248
    13 24434524
    14 28034800
    15 31635077
    16 35235337
    17 37535320
    18 40005299
    19 1382449362829 2409363
    20 1882532
    21 3812808
    22 5002904
    23 6453013
    24 8633077
    25 9883082
    26 11233088
    27 13733396
    28 16363727
    29 18363962
    30 20384196
    31 23984481
    32 27584755
    33 31185030
    34 35045319
    35 37445309
    36 40005288
    37 1382442362675 2408887
    38 1882525
    39 3812803
    40 5002902
    41 6733018
    42 8493070
    43 9883077
    44 11233083
    45 13733393
    46 16363728
    47 18363961
    48 20384192
    49 23984473
    50 27584745
    51 31185018
    52 35045306
    53 37445298
    54 40005278


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *