average for unknown range

Try running this:

Sub Avg()
    With Range("B" & rows.Count).End(xlUp)
        .Offset(1).Formula = "=AVERAGE(" & Range("B2:B" & .row).Address & ")"
    End With
End Sub