Excel macro to total columns regardless of the number of rows

apitech used Ask the Experts™

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.



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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

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

Open in new window

Top Expert 2010

Well done, Saqib :)
Thanks, Patrick, but yours is a good teacher.


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!


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial