Auto Sum range with multiple columns.

B

BRC

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
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top