Average column

You can assign a variable to the lastrow of column D ...for example -

dim lrow as long

lrow=range("D" & rows.count).end(xlup).row
range("F2").formulaR1C1 = "=AVERAGE(RC[-2]:R[" & lrow & "]C[-2])"

You do not require the Select statements and even the scroll down statements.