Declare a variable at the top of the sub to capture the row number -1

Dim lngRow As Long

And then get the current row number less 1 and insert the sum function at
the current cell

lngRow = ActiveCell.Row - 1
ActiveCell.Formula = "=Sum(D2:D" & lngRow & ")"

--
Kevin Backmann


"Annoyed Accountant" wrote:

This has got to be easier than I am making it. I need (via VBA) to locate the
end of a column of numbers, go one below the numbers (a blank cell) and enter
a formula that will calculate the sum of the numbers above. Since the amount
of data changes, this is what I have so far:

Range("D8").Select ' this is where my data starts (absolute reference)
Selection.End(xlDown).Select ' goes to last cell of data
Cells.Offset(0, 1).Select ' goes down one cell from that

Now, how do I enter sum(d8:?) How do I refer to the offset cell without
using absolute references in my formula? I tried using a macro, but excel
automatically used absolute references. Thanks in advance for the help. This
shouldn't be difficult!