How to: Programmatically Automatically Fill Ranges with Incrementally Changing Data
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 application-level projects for Excel 2013 and Excel 2010. 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 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
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);
}
Compiling 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.
See Also
Tasks
How to: Programmatically Refer to Worksheet Ranges in Code
How to: Programmatically Apply Styles to Ranges in Workbooks
How to: Programmatically Run Excel Calculations Programmatically