Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Macro to subtotal Column at end of list

10 views
Skip to first unread message

Cara Connor

unread,
May 8, 2002, 2:34:21 PM5/8/02
to
I know I've seen this before somewhere, but I have searched through
the archives and have been unable to find for what I am looking.

I would like a macro to subtotal (in bold print) a particular Column,
one or two rows below the final entry.

Thanks in advance.
Cara

And special thanks to Conrad Carlberg(I'm a C squared, too!) & J.E.
McGimpsey for your help with my previous questions. I NEVER would
have been able to figure out that stuff on my own. It was way beyond
my current capability. You guys rule!

Michael Craiss

unread,
May 8, 2002, 5:52:50 PM5/8/02
to
Hi Cara,

If your first cell is C4 then following procedure should work. Adjust C4 for
your needs - or even better: pass the first cell as an argument to the
procedure.

Sub SubTotal()
Dim firstcell As Range
Dim lastcell As Range

Set firstcell = Range("C4")
Set lastcell = firstcell.End(xlDown)
with lastcell.offset(1,0)
.Formula = "=SUM(" & Range(firstcell, lastcell).Address(False, False) &
")"
.font.bold=true
End Sub

Regards
Michael

Cara Connor <cara_...@hotmail.com> schrieb in im Newsbeitrag:
8c8bddd8.02050...@posting.google.com...

John Green

unread,
May 8, 2002, 6:06:24 PM5/8/02
to
Cara,

If you have selected the cell under the last entry in thecolumn, you can use
the following to sum from row 2 to the row above the selected cell:

ActiveCell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

If you want to find the last cell in the column, say A, automatically, use:

Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).FormulaR1C1 _
= "=SUM(R2C:R[-1]C)"


--

John Green
Sydney
Australia


"Cara Connor" <cara_...@hotmail.com> wrote in message
news:8c8bddd8.02050...@posting.google.com...

David McRitchie

unread,
May 9, 2002, 10:04:45 AM5/9/02
to
Hi Cara,
The following macro will place a formula below the end of
Column F.

Sub Macro2()
Dim celladdr As String
celladdr = Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Address(1, 1)
Range(celladdr).Font.Bold = True
Range(celladdr).Formula = "'=SUM(F2:OFFSET(" & celladdr & ",-1,0))"
End Sub

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


"Cara Connor" <cara_...@hotmail.com> wrote in message news:8c8bddd8.02050...@posting.google.com...

0 new messages