Sum Formula in Excel
 

Hi Guys,
hopefully somebody can help.

I have columns of data, and I want to sum up at the bottom of each
column the total for each column. I know how to do this, but the range
of the columns can change each time because the number rows can be
different for each month.

So if i find the last row of each column, how do i then select from
there up to calculate the sum??

What I'm trying to do is, if a column contains all zero's or a row
contains a whole row of zero's then delete the entire row/column. Is
there an easier way of doing this??

Thanks Christoph



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sum Formula in Excel
 

Hi chrismania

This example will work for Column C

Sub test()
With Range("C" & Rows.Count).End(xlUp).Offset(2, 0)
.Formula = "=SUM(C1:C" & .Row - 3 & ")"
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"chrismania" wrote in message ...
Hi Guys,
hopefully somebody can help.

I have columns of data, and I want to sum up at the bottom of each
column the total for each column. I know how to do this, but the range
of the columns can change each time because the number rows can be
different for each month.

So if i find the last row of each column, how do i then select from
there up to calculate the sum??

What I'm trying to do is, if a column contains all zero's or a row
contains a whole row of zero's then delete the entire row/column. Is
there an easier way of doing this??

Thanks Christoph



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sum Formula in Excel
 

something like this will work for the rows
Sub delnonums()
lastrow = Cells(65536, "a").End(xlUp).Row
For x = lastrow To 1 Step -1
If Application.Sum(Range(Cells(x, 1), Cells(x, 256))) = 0 _
Then Cells(x, "a").Rows.Delete
Next
End Sub



"chrismania" wrote in message
...
Hi Guys,
hopefully somebody can help.

I have columns of data, and I want to sum up at the bottom of each
column the total for each column. I know how to do this, but the range
of the columns can change each time because the number rows can be
different for each month.

So if i find the last row of each column, how do i then select from
there up to calculate the sum??

What I'm trying to do is, if a column contains all zero's or a row
contains a whole row of zero's then delete the entire row/column. Is
there an easier way of doing this??

Thanks Christoph



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sum Formula in Excel
 

Thanks Guys



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sum Formula in Excel
 

Just another similar idea using R1C1 Notation.

Sub Demo()
Cells(Rows.Count, "C").End(xlUp)(2).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
End Sub

Be careful on a Row that has both +5, and -5, as the Sum would equal zero.
I don't think you want that row deleted. I'm not sure what a good code
would be if you could have that situation. Maybe something similar to
this...

Sub Demo2()
Dim r As Long
With WorksheetFunction
For r = 10 To 1 Step -1
If .Max(Rows(r)) = 0 And .Min(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End With
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"chrismania" wrote in message
...
Hi Guys,
hopefully somebody can help.

I have columns of data, and I want to sum up at the bottom of each
column the total for each column. I know how to do this, but the range
of the columns can change each time because the number rows can be
different for each month.

So if i find the last row of each column, how do i then select from
there up to calculate the sum??

What I'm trying to do is, if a column contains all zero's or a row
contains a whole row of zero's then delete the entire row/column. Is
there an easier way of doing this??

Thanks Christoph