Share via

How to: Programmatically automatically fill ranges with incrementally changing data

  • Article

Applies to: yesVisual Studio noVisual 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:

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.

See also