Have come up with this which does the job for now but there has to be
a better method than for/each

ActiveCell is A4

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1, 0).End(xlUp))

MsgBox rng3.Address 'returns A1:A3

For Each cell In rng3
If cell.Value = "" Then
Set rng3 = ActiveCell.Offset(-1, 0)

MsgBox rng3.Address 'returns A3

End If
Next

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"


Gord


On Thu, 24 Nov 2011 15:20:53 -0800, Gord Dibben
wrote:

I am stuck with VBA syntax.


A1 has value of 123

A2 is blank

A3 also has value of 123

In A4 need a SUM range of A3 only

If A1:A3 all have values then need SUM range of A1:A3

This is what I have now but does not do the variable trick.

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1,0).End(xlUp))

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"


Gord