PDA

View Full Version : summation



Mykasd
02-27-2008, 02:30 PM
I need to be able to write a quick code for summing cells in a column when the row count is constantly changing. For example, one set might have 5 rows today, but 7 rows tomorrow. Is there a way to do an offset command that selects the range and sums it or something that accounts for this change of rows?

tstav
02-27-2008, 03:05 PM
Sub SumCells()
Dim lngStartRow, lngEndRow, lngColumn As Long
Dim mySheet As Worksheet
Set mySheet = ActiveWorkbook.Worksheets(1)
'Suppose the cells to be summed are in Column 2 of Worksheets(1)
'and they start from Row 1
'You may change these settings as you see fit
lngColumn = 2
lngStartRow = 1
'Find the last row of column 2
lngEndRow = mySheet.Cells(mySheet.Rows.Count, lngColumn).End(xlUp).Row
'Proceed with the summation
mySheet.Cells(lngEndRow + 1, lngColumn).Value = _
WorksheetFunction.Sum _
(Range(Cells(lngStartRow, lngColumn), Cells(lngEndRow, lngColumn)))
End Sub

Bob Phillips
02-27-2008, 05:07 PM
If they are the only things within the column, just use

=SUM(C:C)

If they are not, how do you know where to start/stop?

trampzju
02-27-2008, 09:41 PM
Assuming you are going to sum column A.

sub sumcol

endrow=range("A65536").end(xlup).row
range("A" & endrow+1).formula="=sum("A1:A" & endrow)"

end sub