Sum Formula in Excel

[chrismania]

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

 

[Ron de Bruin]

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

 

[Don Guillett]

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]

Thanks Guys

 

[Dana DeLouis]

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