Add Sum to the columns at the last row

[vivi]

Hi there, I am using this code here but it doesn't give me the result I
desire as I have a 36 months starting from Column V and data starts at row 7.
The problem is that the starting point is always the same, but my end point
varies depending the number of entries. I would like to add a formula to the
end of each month.

The code I am using is:

Sub Marco()

Dim lastrow2 As Range

ActiveWorkbook.Names.Add Name:="lastrow4", _
RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp)

Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _
Offset(1, 0)

lastrow2.FormulaR1C1 = "=SUM(R7C10:lastrow4)"

End Sub

Instead of calculating the column itself, it calculates from the start point
to the last cell that has a number

Can you help please?

Thanks a lot

Viv

 

[Jacob Skaria]

Sub Marco()

Dim lastrow2 As Range

ActiveWorkbook.Names.Add Name:="lastrow4", _
RefersToR1C1:=ActiveSheet.Cells(Rows.Count, "C").End(xlUp)

Set lastrow2 = ActiveSheet.Cells(Rows.Count, "C").End(xlUp). _
Offset(1, 0)

lastrow2.Formula = "=SUM(C1:C" & lastrow2.Row - 1 & ")"

End Sub

 

[Don Guillett]

This sums c7:v? whatever the last row in col C is. Does it below last row in
col C

Sub sumcolc()
lr = Cells(Rows.Count, "c").End(xlUp).Row
Cells(lr + 1, "c").Formula = "=sum(c7:v" & lr & ")"
End Sub

 

[vivi]

Hi There

Thanks for this, this work perfectly

At the moment i just fix to column c as I am just playing around with it

I have actually 250 columns to insert this formula

Do you have a suggestion to loop this instead of writing 250 lines of code
for each column?

I thought of using similar code to set the column as a variant and loop it
by adding 1 each time. But i keep having an error and refuse to let me go
further !!!

Thanks a lot

 

[vivi]

Forgot to give you what I've written, it didn't give me an error but it takes
a long time and froze excel, but it should be short as only a small test ...I
had to stop it by pressing esc

Sub Marco()

Dim lastrow2 As Range

Do Until qcol = 20

qcol = 8

ActiveWorkbook.Names.Add Name:="lastrow4", _
RefersToR1C1:=ActiveSheet.Cells(Rows.Count, [qcol]).End(xlUp)

Set lastrow2 = ActiveSheet.Cells(Rows.Count, [qcol]).End(xlUp). _
Offset(1, 0)

lastrow2.Formula = "=SUM(C1:C" & lastrow2.Row - 1 & ")"

qcol = qcol + 1

Loop

End Sub

 

[Don Guillett]

This will use col C as the longest row and put the sum formula for c:H.
Modify to suit
If c is not the longest row, use another.

Sub sumcolc()
lr = Cells(Rows.Count, "c").End(xlUp).Row
Cells(lr + 1, "c").Resize(, 6).Formula = _
"=sum(c1:c" & lr & ")"

End Sub