Find range of the column where the active cell is-CodePudding

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?

CodePudding user response：

Average for Columns of a Range

Sub AverageTest()
    
    Const rgAddress As String = "A1:ALL30"
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim rg As Range: Set rg = ws.Range(rgAddress)
    Dim arg As Range: Set arg = rg.Resize(1).Offset(rg.Rows.Count)
    arg.Formula = "=AVERAGE(" & rg.Columns(1).Address(, 0) & ")"
    
End Sub

CodePudding user response：

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)