Excel 2007 Sum variable length column.

Question

I'm trying to make a macro to sum a variable length column. I've tried a few different things but they haven't worked. What I want to do is:

If the active cell is cell B17, I'd like B17 to equal the sum of B1 to B16. Similarly if the active cell is cell D22, I'd like D22 to equal the sum of D1 to D21.

So basically it is summing all the cells above the active cell, in the selected column.

Thanks, James.

Answer 1

Can a macro click the [sum] toolbar button (the funny E - sigma) and press enter?

Answer 2

Try this and tweak for special cases (row 1 etc..)

Public Sub abcd()
  Dim rw As Long
  Dim cl As Long
  Dim s As Double
  Dim rng As Range

  rw = ActiveCell.Row
  cl = ActiveCell.Column
  Set rng = Range(Cells(1, cl), Cells(rw - 1, cl))

  s = Application.WorksheetFunction.Sum(rng)
  MsgBox s
  ActiveCell.Value = s
End Sub

Answer 3

=SUM(R1C:R[-1]C) or am I missing something?