AutoFill


Filling a Range Down


You do not have to select the cells first


objRange.Auto Fill(Destination:=Range("B2"), _ 
                  Type:=xlAutoFillType.xlFillDefault)

The Destination is the range of cells to be filled. This cell range must include the actual source data in order to create the Auto Fill.
If the Type argument is left blank or you choose xlFillDefault then Excel will attempt to select the most appropriate fill type based on the source data.


Example when cell contains values




Example when cell contains formulas


One potential problem with the Auto Fill method is that when it 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.
So if autocalculation is switched off, the formulas will be correct but the values will be incorrect.
This can be overcome by invoking a Calculate method after the Auto Fill has been executed.

Application.Calculate 


Filling Down with Values

Fills down from the top cell or cells in the specified range to the bottom of the range.
The contents and formatting of the cell in the top row of a range are copied into the rest of the rows in the range.

Selection.FillDown 

This example copies the contents in cell "A1" down to the whole range "A2:A10".

Range("A1:A10").FillDown 


Filling Down with Formulas

Range("C1").Formula = "=A1+B1" 
Range("C1:C7").FillDown



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext