Averaging column data in VBA

Resolved · Medium Priority · Version 2010

Wed 2 Nov 2011, 15:12 Delegate Peter said...

Peter has attended:
Excel VBA Intro Intermediate course

Averaging column data in VBA

Hi, I was hoping you could help me find a nicer way of averaging the data in a column of unknown length. Just telling the cell exactly what it should contain does work but is not very flexible and i'm sure there is a nicer way of doing it. This does work because excel doesnt count cells which are empty for the averaging divide.

Sheets(SheetName).Range("e5").Value = "=AVERAGE(e12:e1048576)"

Thanks,

Peter

Sat 5 Nov 2011, 20:07 Trainer Simon said...

RE: Averaging column data in VBA

Hi Peter,

Thank you for your question and welcome to the forum.

Try this code:

Sub UseExcelFunctions()

Dim iColumnCount As Integer
Dim lRowCount As Long
Dim iCol As Integer
Dim dAverage As Double

Sheets("Sheet2").Activate

iColumnCount = ActiveSheet.UsedRange.Columns.Count
lRowCount = ActiveSheet.UsedRange.Rows.Count

For iCol = 1 To iColumnCount

dAverage = WorksheetFunction.Average(Columns(iCol))

Cells(lRowCount + 2, iCol).Value = dAverage

Next iCol

End Sub

I hope this helps.

Regards

Simon