Get Last Row
i = Range("B1").column
Lastrw= Cells(rows.count, i).End(xlup).row
Cells(lastrw + 1, i).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
C means the column containint the formula
C1 means Column A (Absolute)
C[1] means the column to the right of the column with the formula
R means the row with the formula
R6 means Row 6 (absolute)
R[-1] means the row above the row with the formula
R[1] would mean the row after the row with the formula
--
regards,
Tom Ogilvy
"Jeremy" wrote:
Dear All,
I've seen various postings regarding this subject but have not been able to
apply them to my application.
What I need to do is append the @Sum function to the last row of an Excel
sheet so it will sum the values in the columns. The spread sheet is filled
dynamically by means of an export from a Lotus Notes database.
Bob Phillips very kindly helped me do something similar for the last column
by doing as follows:
LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
then add a formula to sum column 2 to that column like so
Cells(2, LastCol + 1).FormulaR1C1 = "=SUM(RC2:RC[-1])"
How can I do the same for the last row? The above works great but I don't
understand why. I can see from help that the "FormulaR1C1" property "returns
or sets the formula for the object€¯ but I dont understand what the "RC"
stands for in the formula above nor what the [-1] indicates.
Any help would be gratefully received.
My apologies for my ignorance!
Jeremy
|