Summing a column in a macro

  • Thread starter Thread starter gfinch
  • Start date Start date
G

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.
 
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)
 
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
 
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
 
Back
Top