AutoSum

Snowman

unread,

Jun 10, 2002, 3:44:52 PM6/10/02

to

I am trying to duplicate the autosum function in excel but through code. I
have some columns that vary in the amount of rows I am trying to sum. How
can I count the rows and then sum those counted rows up? As always...
Thanks to those who participate in these NG.

--
Thank You,

Brady Snow
Dallas, Texas

Wilson

unread,

Jun 10, 2002, 4:14:56 PM6/10/02

to

If you know the location of the top of the column and there are no blank
cells in the column
Range("H3").End(xlDown).Offset(1,0)Select
CommandBars.FindControl(ID:=226).Execute 'Autosum
CommandBars.FindControl(ID:=226).Execute 'Again to exit edit mode
HTH
"Snowman" <bra...@lmctx.com> wrote in message
news:uvEvKnLECHA.1764@tkmsftngp05...

Tom Ogilvy

unread,

Jun 10, 2002, 4:15:25 PM6/10/02

to

Jim Rech posted this previously:

From: Jim Rech (jar...@kpmg.com)
Subject: Re: Using AutoSum in VBA
Newsgroups: microsoft.public.excel.programming

Date: 1999/06/18

I don't know if you saw this in I post I did a couple weeks ago, Tom, but
you can run AutoSum programmatically like this:

Sub DoAutoSum()
CommandBars.FindControl(ID:=226).Execute 'AutoSum
If Selection.Cells.Count = 1 Then

CommandBars.FindControl(ID:=226).Execute 'Again to exit edit mode

End If
End Sub

You need the second execute if one cell is selected or else you'll be left
in edit mode. When a sum is being added to 2 or more columns/rows that
doesn't happen.

Jim==================Here is his latest advice because of changes in Excel
2002:From: Jim Rech (jar...@kpmg.com)Subject: AutoSum Toolbutton Execute
Newsgroups: microsoft.public.excel.programming Date: 2001-05-17 07:38:02 PST
In case anyone uses the Execute method with the AutoSum toolbar button I
just discovered it doesn't work with Excel 2002 because the control has
changed type. It's now a msoControlSplitButtonPopup because it can so
several math functions in addition to a Sum. So this is the way to handle
it across versions I think:

Sub DoAutoSum()
Dim x As CommandBarControl
Set x = CommandBars.FindControl(ID:=226)
If Val(Application.Version) >= 10 Then _
Set x = x.Controls(1)
x.Execute
If Selection.Cells.Count = 1 Then
x.Execute 'Again to exit edit mode if only one cell is selected
End If
End Sub
--
Jim Rech
Excel MVP--Regards,Tom Ogilvy

----------------------------------------------------------------------------
----

"Snowman" <bra...@lmctx.com> wrote in message
news:uvEvKnLECHA.1764@tkmsftngp05...

Otto Moehrbach

unread,

Jun 10, 2002, 4:27:43 PM6/10/02

to

Brady

From what you say, I understand that you want to get the sum of your
range and not that you're interested in duplicating the AutoSum feature. If
so, read on.

You don't say how your rows are determined. If the cells you want to
sum are from A2 to the last occupied cell in Column A, you can set the range
like this:

Set RangeToSum = Range("A2", Range("A" & Rows.Count).End(xlUp). Address)

You can get the sum with:

MsgBox Application.WorksheetFunction.Sum(RangeToSum)

Note: The above also works without the "WorksheetFunction".

HTH Otto

"Snowman" <bra...@lmctx.com> wrote in message
news:uvEvKnLECHA.1764@tkmsftngp05...

Snowman

unread,

Jun 10, 2002, 4:55:28 PM6/10/02

to

That is what I needed!!

--
Thank You,

Brady Snow
Dallas, Texas
"Otto Moehrbach" <ot...@worldnet.att.net> wrote in message
news:u08C00LECHA.2040@tkmsftngp05...