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
--
Cheers

Peter

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

--


Gary


"Peter Thomas" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> --
> Cheers
>
> Peter

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)

"Peter Thomas" wrote:

> 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
> --
> Cheers
>
> Peter
>

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

=sum(sheet1!a:a)



Peter Thomas wrote:
>
> 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
> --
> Cheers
>
> Peter

--

Dave Peterson