How to sum a row using VBA where number of rows is relative

[RyanScharfy]

Quick dumb question. I've blown right past this in my VBA for Excel "self education" because usually the Subtotal function works for what I need.

However, in this case I don't want to use it. My macro should generate a sum of all the numbers in column F in the blank cell at the end of column F. The numbers are listed from F2 down, with no breaks. (FYI column A through E hold descriptive characters).

Every time I run the report, there will be a different number of rows, so it's not as easy as putting in a static formula.

Any quick and easy ideas? I thought this might be in the FAQ, but couldn't find it.

Thanks.

 

[PHV]

Feel free to search this forum for Dynamic Range

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Storyteller]

Hello RyanScharfy,
Searching for Dynamic Range will give you examples to think about and study. However, if you are needing a quick solution:

Sub CalculateColumnF()

Dim LastCell as Range
Dim TotalFormula as String

Set LastCell = Range("F2").End(xlDown)

LastCell.Select
ActiveCell.Offset(1,0).Select
TotalFormula = "=Sum(F2:" & LastCell.Address & ")"
ActiveCell.Formula = TotalFormula

End Sub

Hope this helps,
Michael

 

[SkipVought]

Ryan,

Why make the user HUNT for the total? Totals at the BOTTOM of a column are really a throwback to the paper, pencil method of calculating totals.

With Excel, you can display a total ANYWHERE! Why not take advantage of this wonderful feature and place totals and other GRAND summary data at the TOP, for everyone to see without having to seach for this important information???



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884