Filling a Range with a Series with Excel VBA

The DataSeries method of the Range object allows you to fill a range with a sequence (arithmetic, geometric, date-based, or AutoFill).
This method programmatically replicates the Fill | Series command available on the Ribbon.

DataSeries(RowCol, Type, Date, Step, Stop, Trend)

Parameters:

• RowCol (optional) – Direction of the series:
  • xlRows → fill across rows.
  • xlColumns → fill down columns.
    If omitted, Excel uses the size of the selected range.
• Type (optional) – The type of series. Possible values:
  • xlDataSeriesLinear (arithmetic, default).
  • xlGrowth (geometric).
  • xlChronological (date series).
  • xlAutoFill (pattern-based autofill).
• Date (optional) – Defines the type of date sequence when Type = xlChronological:
  • xlDay (days, default).
  • xlWeekday (weekdays only).
  • xlMonth (months).
  • xlYear (years).
• Step (optional) – Increment of the series (default = 1).
• Stop (optional) – The upper limit of the series. If omitted, Excel fills the entire selected range.
• Trend (optional) – Boolean.
  • True → generates arithmetic or geometric progression.
  • False → generates a list.

Examples

1. Arithmetic Progression
   The following macro fills range A1:A6 with an arithmetic progression starting at 0, step = 2, ending at 10.
   Result: 0, 2, 4, 6, 8, 10

Sub Progr1()
    Range("A1").Value = 0
    Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlDataSeriesLinear, _
        Step:=2, Stop:=10
End Sub

2. Geometric Progression
   This macro fills range B1:B5 with a geometric progression starting at 1, multiplied by 3 each step.
   Result: 1, 3, 9, 27, 81

Sub Progr2()
    Range("B1").Value = 1
    Range("B1:B5").DataSeries Rowcol:=xlColumns, Type:=xlGrowth, Step:=3
End Sub

3. Date Progression
   This macro fills range C1:C4 with dates that increase by one month each step.
   Result: 01/01/2011, 01/02/2011, 01/03/2011, 01/04/2011

Sub Progr3()
    Range("C1").Value = "1/01/2011"
    Range("C1:C4").DataSeries Rowcol:=xlColumns, Type:=xlChronological, _
        Date:=xlMonth
End Sub