Invoke AutoSum From VBA?

[PeteCresswell]

I've got a cell selected.

Now I'd like the macro to invoke the AutoSum icon (the little sigma..)
that sums the columns above the selected column.

But when I try to record a macro, it comes up with the result of the
AutoSum (e.g. "=SUM(D38)") instead of a call to AutoSum.

Is there a way?

The agenda is tb able to create a sum per the rules built into the
AutoSum icon and make it bold with a single Ctl+(letter) stroke.

I guess the obvious is to start at the selected cell, iterate upwards
until a blank cell is found, and then do an =Sum() of what's between.

But before I did that, I wanted to see if I was missing something with
AutoSum.

 

[Peter T]

Alt =

Regards,
Peter T

 

[Bernie Deitrick]

With the cells with the values to sum selected:
CommandBars.FindControl(Id:=226).Execute



To create the formula:

Sub SumAtBottomOfCurrentColumn()
Dim myCell As Range
Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2)
With myCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
Cells(2, .Column)).Address(False, False) & ")"
End With
End Sub


HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

Depending on the version of excel you're using, you may see a dropdown arrow on
that autosum icon. This allows you to use other functions, too.

Here's a post from Jim Rech posted that takes this into account:

This is one way to make Autosum run by code:

Sub DoAutoSum()
Dim x As CommandBarControl
Set x = CommandBars.FindControl(ID:=226)
If Val(Application.Version) > 9 Then _
Set x = x.Controls(1)
x.Execute 'AutoSum
If Selection.Cells.Count = 1 Then
x.Execute 'Again to exit edit mode
End If
End Sub

Here's another:

Sub DoAutoSumWithSendKeys()
SendKeys "%={Enter}"
End Sub

 

[(PeteCresswell)]

Per Dave Peterson:

Sub DoAutoSum()
Dim x As CommandBarControl
Set x = CommandBars.FindControl(ID:=226)
If Val(Application.Version) > 9 Then _
Set x = x.Controls(1)
x.Execute 'AutoSum
If Selection.Cells.Count = 1 Then
x.Execute 'Again to exit edit mode
End If
End Sub

That's the one I went with. Works like a champ!

Thanks Dave.

Thanks Bernie.

Thanks Peter.