Summing a column in a macro

[gfinch]

I would like to sum a column in a macro I have recorded. It would start
at the same cell (G9) everytime but the length of the column will vary
from day to day. I would like to put the sum in the first available
cell at the end of the column. What would be the best way to
accomplish this. Thank you in advance.

 

[Bob Phillips]

Try this

Dim cRows As Long

cRows = Cells(Rows.Count, "G").End(xlUp).Row
Cells(cRows + 1, "G").FormulaR1C1 = "=SUM(R9C:R" & CStr(cRows) & "C)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[gfinch]

Bob,
Thanks for the help. Works like it should. I appreciate the help all
of you give through this forum. Keep up the good work.
Gib

 

[Bob Phillips]

Thanks Gib, your feedback is appreciated.

Bob

 

[Tim Barlow]

Try something like:

Sub sumColumn()

Dim firstRow As Long
Dim lastRow As Long
Dim aColumn As Integer

aColumn = 7 'Column G
firstRow = 9
lastRow = Cells(65536, aColumn).End(xlUp).Row

If lastRow >= firstRow Then _
Cells(lastRow + 1, aColumn) = _
"=SUM(R" & firstRow & "C:R" & lastRow & "C)"

End Sub

This assumes that there is no total already entered (or
else the existing total would be included in the new
total).

Tim