Can anyone tell me if there is vba command to auto sum a range similar
to pushing the autosum button ˆ‘ in excel 2007? In this situation the
cells may not always be in the same location so hard coding not
practical. I can select the range, but can't find command to autosum.
I found the the snippet below but it generates error in excel 2007.
Thanks for any advice. BRC
CommandBars.FindControl(ID:=226).Execute 'AutoSum

Where would you like the results to appear?

Below leftmost column of selected range.

Sub Sum_Range()
Set rng = Selection
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Value = WorksheetFunction.Sum(rng)
End Sub

Below or top right depending upon number of columns.

Sub Sum_Range()
Set rng = Selection
If rng.Columns.Count 1 Then
Set rng1 = rng.Offset(0, rng.Columns.Count).Resize(1, 1)
rng1.Value = WorksheetFunction.Sum(rng)
Else
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Value = WorksheetFunction.Sum(rng)
End If
End Sub


Gord Dibben MS Excel MVP

On Mon, 11 Jan 2010 12:47:16 -0800 (PST), BRC
wrote:

Can anyone tell me if there is vba command to auto sum a range similar
to pushing the autosum button ? in excel 2007? In this situation the
cells may not always be in the same location so hard coding not
practical. I can select the range, but can't find command to autosum.
I found the the snippet below but it generates error in excel 2007.
Thanks for any advice. BRC
CommandBars.FindControl(ID:=226).Execute 'AutoSum