CODEWITHSUNDEEP
excelvba
LDM31
LDM31

Reputation: 15

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?

Upvotes: 1

Views: 72

Answers (2)

VBasic2008
VBasic2008

Reputation: 54948

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

Upvotes: 2

norie
norie

Reputation: 9867

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)

Upvotes: 1

Related Questions