Auto-filling a Range with Sequence Elements with Excel VBA

The AutoFill method of the Range object performs auto-filling of a range with sequence elements. The AutoFill method differs from the DataSeries method in that the range in which the progression will be placed is explicitly specified. The AutoFill method simulates the action of copying data to a range when the user places the mouse pointer on the fill handle of the source range and drags it down or to the right, selecting the entire range into which the source data is transferred.

expression.AutoFill(Destination, Type)

• expression — a required element that specifies the range from which filling begins.
• Destination — a required parameter that defines the range to be filled. This range must include the range specified in expression.
• Type — an optional parameter that specifies the type of fill. The permissible values are the following XlAutoFillType constants:
  xlFillDefault, xlFillSeries, xlFillCopy, xlFillFormats, xlFillValues, xlFillDays, xlFillWeekdays, xlFillMonths, xlFillYears, xlLinearTrend, xlGrowthTrend.
  By default, the type of fill that best matches the data in the range specified in expression is used.

For example, the following instructions  fill the range A1:A5 with the terms of an arithmetic progression, where the first two terms are 1 and 3 (i.e., the values that were previously entered into cells A1 and A2) .

Sequences. Arithmetic Sequence

Sub Progr4()
    Range("A1").Value = 1
    Range("A2").Value = 3
    Range("A1:A2").AutoFill Destination:=Range("A1:A5"), Type:=xlLinearTrend
End Sub

Listing demonstrates generating several terms of a geometric progression on a worksheet with the same two initial values in the range B1:B5 .

Listing. Sequences. Geometric Sequence

Sub Progr5()
    Range("B1").Value = 1
    Range("B2").Value = 3
    Range("B1:B2").AutoFill Destination:=Range("B1:B5"), Type:=xlGrowthTrend
End Sub

The following instructionsoutput into the range C1:C3 the sequence of values Summer 2010, Summer 2011, and Summer 2012 with a step of 1, determined by default by the AutoFill method.

Sequences. AutoFill

Sub Progr6()
    Range("C1").Value = "Summer 2010"
    Range("C1").AutoFill Destination:=Range("C1:C3"), Type:=xlFillSeries
End Sub

The following outputs into the range D1:D3 the first three items of a list — month names, starting with January.

Sequences. Months

Sub Progr7()
    Range("D1").Value = "January"
    Range("D1").AutoFill Destination:=Range("D1:D3"), Type:=xlFillSeries
End Sub

The following instructions copy the contents of cell E1 into all cells of the range E1:E3.

Sequences. Copying

Sub Progr8()
    Range("E1").Value = "January"
    Range("E1").AutoFill Destination:=Range("E1:E3"), Type:=xlCopy
End Sub