Find range of the column where the active cell is

I have 1000 columns each containing 30 rows of data in excel. I want to write a Macro that will find the average of each column. I want to use Range("A31:ALL31").Value = "=AVERAGE(current column's range)" but i don’t know how to get the range of the active column. Is there a way to do that?

>Solution :

Not 100% sure exactly what you want but this code will put a formula in row 31 from column A to column ALL (that is a column), that averages the rows above starting at row 2.

    Range("A31:ALL31").FormulaR1C1 = "=AVERAGE(R2C:R[-1]C)"

Here’s an example of the formula(s) that code will produce.

=AVERAGE(A$1:A30)