Auto Fill Method

This important method performs an autofill on the cells in the range. Its syntax is:

RangeObject.AutoFill(Destination, Type)

Here Destination is the Range object whose cells are to be filled. The destination must include the source range—that is, the range that contains the data to use for the autofill.

The optional Type parameter specifies the fill type. It can be one of the following XlAutoFillType constants (note that the Excel documentation refers to a nonexistent

XlFillType enum):

Enum XlAutoFillType xlFillDefault = 0 xlFillCopy = 1 xlFillSeries = 2 xlFillFormats = 3 xlFillValues = 4 xlFillDays = 5 xlFillWeekdays = 6 xlFillMonths = 7 xlFillYears = 8 xlLinearTrend = 9 xlGrowthTrend = 10 End Enum

If this argument is xlFillDefault or is omitted, Excel will attempt to select the most appropriate fill type, based on the source data.

To illustrate, consider the code:

Range("A1:B1").AutoFill Range("A1:K1")

which autofills cells C1 through K1 using the source data in cells A1 and B1. If A1 contains 1 and B1 contains 2, then this code will fill the destination cells with consecutive integers starting at 3 (in cell C1). Note that cells A1 and B1 are included in the destination range.

As another illustration, consider the worksheet in Figure 19-3, where cell B1 contains the formula:

Figure 19-3. Worksheet to autofill range B1:B5

The code:

Range("B1").AutoFill Range("B1:B5")

will produce the output shown in Figure 19-4.

Figure 19-4. Autofilling B1:B5 in Figure 19-3

Figure 19-4. Autofilling B1:B5 in Figure 19-3

We should mention one source of potential problems with the AutoFill method. Apparently, when AutoFill is executed, the formula in the source cell is copied, with changes, to other cells. However, the value of the source cell is also copied, but without changes. Thus, if autocalculation is off, the formulas in the autofilled cells will be correct but the values will be incorrect. To fix this, just invoke the Calculate method.

0 0

Post a comment