Insert SUM function for range

  • Thread starter Thread starter Denis Petrov
  • Start date Start date
D

Denis Petrov

Dear All,

I have a variable range of data in a column. Exactly at
the bottom of this column I need to insert a SUM function
for the numbers above (not just calculate the totals). I
tried
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0).Formula
= "=SUM(a2:(Range("a6").End(xlDown))"
but it's not working

Please advise!

Thanks.

Denis Petrov
 
Denis,

I like to work with R1C1 notation:
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0).FormulaR1C1 _
= "=SUM(R2C:R[-1]C)"

Since there is no number after 'C', the formula is generic for any column.
And it doesn't matter if your worksheet displays columns with alpha or
numeric.
 
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0).Formula _
= "=SUM(a2:" & Range("a6").End(xlDown).Address & ")"

or

Activesheet.Range("A6").End(xldown).Offset(1,0).FormulaR1C1 = _
"=Sum(R2C:R[-1]C)"
 
To add to what you have gotten, if you want just the sum without the formula
Sub sumem()'from a2 to a2 end xl down
ActiveSheet.Range("a6").End(xlDown).Offset(1, 0) = _
Application.Sum(Range(Cells(2, 1), Cells(Range("a6").End(xlDown),1)))
End Sub
 
Back
Top