Absolute reference problem - Excel 2007

[Peter Thomas]

Hi,
I have to obtain the sum of a column and then have to have that total
appear in another sheet of the workbook every month in order that I
can keep a track of each month's expenses - so far that is no problem.
However, the length of the column varies each month so I am prevented
from using the absolute reference function - can anyone please suggest
how I may overcome this? I am using Excel 2007

 

[Gary Keramidas]

one way, assuming the data starts in a2 on sheet1

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

ws.Range("A" & lastrow + 1).Formula = "=sum(A2:A" & lastrow & ")"

End Sub

 

[Guest]

The sum function ignores empty cells. You can have the end row be larger
than the maximum for any month. Make it the last row of the worksheet which
is 65536 for Excel 2003. Excel 2007 can be larger. Make it sufficiently
large enough so you won't have problems in the future.


=sum(A1:A65536)

 

[Dave Peterson]

If you don't put any numbers below the data or above the data, you could just
use:

=sum(sheet1!a:a)