try this

Option Explicit

Sub avgCol()
Dim lastrow As Long
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row

Range("D" & lastrow + 1).Formula = "=average(D1:D" & lastrow & ")"
End Sub

--


Gary


"KnightRiderAW" <KnightRiderAW@discussions.microsoft.com> wrote in message
news:6FD89C84-F2FE-4666-8920-B987EE542588@microsoft.com...
>I have a Excel file that is imported in from another program as a CSV
>import.
> What I want to do is create a macro that does many different things to
> format the sheet to what we need. I can accomplish all, except the
> following:
>
> I want to be able to have a line at the bottom that has a formula such as
>
> =SUM(First row of data:Last Row of Data)/The number of rows of data
>
> (The total number of rows could be anywhere from 200 to 2000.)
>
> A small sample would be the following:
>
> A B C D
> 1 Item1 $0.50 EACH 3.0
> 2 Item2 $1.50 CASE 1.5
> 3 Item3 $0.75 EACH 4.5
> 4 Item4 $2.00 EACH 0.75
>
> Average of D: =SUM(D1:D4)/4
>
> Thanks for any help!