macro to sum an unknown range

  • Thread starter Thread starter susiel
  • Start date Start date
S

susiel

I have a worksheet that I import data into weekly. I have a macro that
formats all the data on separate sheets, etc. I'm trying to add a
macro that will sum the totals with a beginning range that is always
the same but the bottom of the range is always different. I use a
relative macro that takes me to the last cell with data to actually put
the total in. Now, how do i get it to total from that cell always up
to row 2?
 
To do with a macro, use this. This is for col D. Change to suit

Sub sumem()
x = Cells(65536, "d").End(xlUp).Row
MsgBox Application.Sum(Range(Cells(2, 4), Cells(x, 4)))
End Sub
 
To put at the bottom of the column
Sub sumem()
x = Cells(65536, "d").End(xlUp).Row
cells(x+1,"d") = Application.Sum(Range(Cells(2, 4), Cells(x, 4)))
End Sub



Don Guillett said:
To do with a macro, use this. This is for col D. Change to suit

Sub sumem()
x = Cells(65536, "d").End(xlUp).Row
MsgBox Application.Sum(Range(Cells(2, 4), Cells(x, 4)))
End Sub
Click to expand...
 
Susie,

To add the SUM formula, use a macro like that below.

HTH,
Bernie

Sub SumAtBottomOfCurrentColumn()
Dim myCell As Range
Set myCell = Cells(65536, ActiveCell.Column).End(xlUp)(2)
With myCell
.Formula = "=SUM(" & _
Range(.Offset(-1, 0), _
Cells(2, .Column)).Address(False, False) & ")"
End With
End Sub
 
Sub Tester1()
Dim rng As Range
Set rng = Cells(Rows.Count, ActiveCell.Column).End(xlUp)(2)
rng.FormulaR1C1 = "=Sum(R2C:R[-1]C)"
End Sub

Regards,
Tom Ogilvy
 
Back
Top