How to: Programmatically automatically fill ranges with incrementally changing data
Applies to: 
Visual Studio 
Visual Studio for Mac
Note
This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here
The AutoFill method of the Range object enables you to fill a range in a worksheet with values automatically. Most often, the AutoFill method is used to store incrementally increasing or decreasing values in a range. You can specify the behavior by supplying an optional constant from the XlAutoFillType enumeration.
Applies to: The information in this topic applies to document-level projects and VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.
You must specify two ranges when using AutoFill:
The range that calls the AutoFill method, which specifies the starting point of the fill and contains an initial value.
The range that you want to fill, passed as a parameter to the AutoFill method. This destination range must include the range that contains the initial value.
Note
You cannot pass a NamedRange control in place of the Range. For more information, see Programmatic limitations of host items and host controls.
Example
private void AutoFill()
{
Excel.Range rng = this.Application.get_Range("B1");
rng.AutoFill(this.Application.get_Range("B1","B5"),
Excel.XlAutoFillType.xlFillWeekdays);
rng = this.Application.get_Range("C1");
rng.AutoFill(this.Application.get_Range("C1","C5"),
Excel.XlAutoFillType.xlFillMonths);
rng = this.Application.get_Range("D1","D2");
rng.AutoFill(this.Application.get_Range("D1","D5"),
Excel.XlAutoFillType.xlFillSeries);
}
Private Sub AutoFill()
Dim rng As Excel.Range = Me.Application.Range("B1")
rng.AutoFill(Me.Application.Range("B1:B5"), Excel.XlAutoFillType.xlFillWeekdays)
rng = Me.Application.Range("C1")
rng.AutoFill(Me.Application.Range("C1:C5"), Excel.XlAutoFillType.xlFillMonths)
rng = Me.Application.Range("D1:D2")
rng.AutoFill(Me.Application.Range("D1:D5"), Excel.XlAutoFillType.xlFillSeries)
End Sub
Compile the code
The first cell of the range that you want to fill must contain an initial value.
The example requires that you fill three regions:
Column B is to include five weekdays. For the initial value, type Monday in cell B1.
Column C is to include five months. For the initial value, type January in cell C1.
Column D is to include a series of numbers, incrementing by two for each row. For the initial values, type 4 in cell D1 and 6 in cell D2.