How to: Automatically Fill Ranges with Incrementally Changing Data
Applies to |
|---|
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type
Microsoft Office version
For more information, see Features Available by Application and Project Type. |
The AutoFill(Range, XlAutoFillType) method of the Range object enables you to fill a range in a worksheet with values automatically. Most often, the AutoFill(Range, XlAutoFillType) 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.
You must specify two ranges when using AutoFill(Range, XlAutoFillType):
The range that calls the AutoFill(Range, XlAutoFillType) 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(Range, XlAutoFillType) method. This destination range must include the range that contains the initial value.
Note
You cannot pass a Microsoft.Office.Tools.Excel.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", missing);
rng.AutoFill(this.Application.get_Range("B1","B5"),
Excel.XlAutoFillType.xlFillWeekdays);
rng = this.Application.get_Range("C1", missing);
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: Refer to Worksheet Ranges in Code
How to: Apply Styles to Ranges in Workbooks
How to: Run Excel Calculations Programmatically
Concepts
Host Items and Host Controls Overview
The Variable missing and Optional Parameters in Office Solutions