Sum a changing range in Excel

[lizI3]

How would I write a vba function to sum a changing range in Excel?

So that I could call the function anywere in my spreadsheet and it would sum up to the first blank line. I was tring to use
.FormulaR1C1 = "sum(" _
& Range(.Offset(-1, 0), .Offset(-1, 0).End(xlUp)) _
.Address(False, False, xlR1C1,activecel&quot"

But I get an error.

thanks
Liz

 

[KenWright]

This will sum all data in a single column from the Activecell down:-

Sub SumData()
Dim myrange As Range
Set myrange = Range(ActiveCell, ActiveCell.Offset(Rows.Count - ActiveCell.Row, 0).End(xlUp))

answer = Application.WorksheetFunction.Sum(myrange)
MsgBox answer

End Sub

Regards
Ken............

 

[KenWright]

If you definitely don't have any blanks in your range of data, or if you have columns of data one on top of each other with at least one blank row between them (but still no blanks in the range to be summed itself), you could trim it down even further:-

Sub SumData()
Dim myrange As Range
Set myrange = Range(ActiveCell, ActiveCell.End(xlDown))

answer = Application.WorksheetFunction.Sum(myrange)
MsgBox answer

End Sub

Regards
Ken.............