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