Excel vba calculate average of a column

Question

I want to calculate average of a column and put the value below.
I wrote this code in VBA but returned value is always 0.

Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
Dim sum As Integer
Dim count As Integer
count = 0
sum = 0
Do While ActiveCell.Value <> ""
    ActiveCell.Offset(1, 0).Activate
    sum = sum + ActiveCell.Value
    count = count + 1
    Loop
ActiveCell.Value = sum / count
ActiveCell.Offset(0, 5).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
End Sub

Answer 1

As @Tahbaza points out, unless your ActiveCell is at the top of the row, it will only count from the row in the column were the active cell is.

Your code also has an error in that it won't count the active cell in the first iteration of the loop so this value will be missed from the average.

I've fixed the two problems in the following set of code, however there would be other improvements (ie not using Select at all) if not for the suggestion following.

Sub Macro4()
   '
   ' Macro4 Macro
   '
   ' Keyboard Shortcut: Ctrl+Shift+C
   Dim sum As Integer
   Dim count As Integer
   count = 0
   sum = 0
   Selection.End(xlUp).Select              '<---Go to the top of the range
   Do While ActiveCell.Value <> ""
       sum = sum + ActiveCell.Value
       count = count + 1
       ActiveCell.Offset(1, 0).Activate    '<---Move down *after* you done the sumcount
   Loop
   ActiveCell.Value = sum / count
   ActiveCell.Offset(0, 5).Select
   Selection.End(xlUp).Select
   Selection.End(xlUp).Select
End Sub

You can achieve your desired outcome with a single line of code as follows. This assumes the cells you wish to have averaged are continuous and there are no empty cells. It also assumes your values start in this first row of the worksheet and there has to be at least two rows of values.

Sub ColumnAverage()
    Cells(1, ActiveCell.Column).End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Average(ActiveSheet.Columns(ActiveCell.Column))
End Sub

Edit: In fact if you want don't need a static value but can use the worksheet function formula in the last cell of the column. Its a bit of a beast but if you break it down it should make sense:

Sub ColumnAverageFormula()
    Cells(1, ActiveCell.Column).End(xlDown).Offset(1, 0).Formula = "=Average(" & Cells(1, ActiveCell.Column).Address(0, 0) & ":" & Cells(1, ActiveCell.Column).End(xlDown).Address(0, 0) & ")"
End Sub