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
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
