Share via


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

  • Document-level projects

  • Application-level projects

Microsoft Office version

  • Excel 2003

  • Excel 2007

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):

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

Working with Ranges

Host Items and Host Controls Overview

The Variable missing and Optional Parameters in Office Solutions