Thread: Average column

As always, it got to 17.00 and I clicked something and lost a working Macro that I now cannot replicate.

I am trying to write a macro for a series of spreadsheets where the column I wish to average (same in each) has varying numbers of rows.

As a very amateur VBA writer, I managed to record a Macro and edit the values (xlDown) so it that looked at a larger range depending on how many rows in the data.

The whole macro converts dates stored as text to numerical value, then copy and pastes it to 'dd/mm/yy hh/mm/ss' then averages associated data in a column F.
The section of macro I broke was along the lines of:

"Range("F2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-2]:R[End(xlDown)]C[-2])"
Range("F2").Select
ActiveWindow.SmallScroll Down:=-18"

I cannot remember exactly what I put in as the R[End(xlDown)] part but it worked once and now cannot replicate... I am trying to enter in cell F2 the average of column D from D2 to the end of the column.

Any ideas?

Many thanks in advance.

Damien

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.