# Excel macro to total columns regardless of the number of rows

on
Hello:

I have an Excel macro that does an Auto-Sum of data in columns, O, P, and Q of my spreadsheet.

That's great.  But, at this point, my spreadsheet has 459 rows of data.  Next month, it may have 500 rows of data.  The following, it may have 420, etc.

My macro is placing the Auto-Sum in row 460.  But, I want it to be versatile and sum the values in these three columns--no matter what the number of rows is.

So, I need one of two things.  I need for someone to tell me either (a) what keystrokes I can use to accomplish this so that I can re-record my macro or (b) help me modify my macro's code below to accomplish this.

Thanks!

Apitech

ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.LargeScroll Down:=14
Range("O460:Q460").Select
Selection.FormulaR1C1 = "=SUM(R[-458]C:R[-1]C)"
End Sub
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Try

Range("O" & Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 3).FormulaR1C1 = "=sum(R2C:R[-1]C)"
Top Expert 2010

Commented:
This bases the formula on the last populated row in Col O:

``````Dim LastR As Long

With ActiveSheet
LastR = .Cells(.Rows.Count, "o").End(xlUp).Row
.Range("O" & LastR + 1 & ":Q" & LastR + 1).Formula = "=SUM(O2:Q" & LastR & ")"
End With
``````
Top Expert 2010

Commented:
Well done, Saqib :)

Commented:
Thanks, Patrick, but yours is a good teacher.

Commented:
Definitely well done, Saqib!  Thank you!

And, thank you, matthewspatrick, for all of the help that you gave me this week!

You guys are geniuses, in every sense of the word!

Apitech

Do more with