0

My code is:

Range("M1").Select
ActiveCell.FormulaR1c1 = "SUM(R[2]C:R[4157]C)"

My problem is what if I have more than 4157 rows. How do I ensure all rows for that column will be added up?

Thanks!

lfrandom
  • 1,013
  • 2
  • 10
  • 32
Jhenn3
  • 1
  • 1
  • 2
  • 4
  • Check out [this question](http://stackoverflow.com/q/14934653/1698517) to familiarize yourself with `xlUp` – Jaycal Jul 29 '13 at 22:27

2 Answers2

1

Try with:

Range("M1").Select
ActiveCell.FormulaR1c1 = "=SUM(R[2]C:R[" & ActiveSheet.UsedRange.Rows.Count & "]C)"

EDIT:

Added the = to the formula, so that Excel understands it is a formula. Otherwise it would just put the text in the cell.

stenci
  • 8,290
  • 14
  • 64
  • 104
  • The above code does the same thing except it doesn't pick up all of the rows. This is what I get (SUM(R[2]C:R[52]C). The previous code comes up with this: SUM(R[2]C:R[53]C) – Jhenn3 Jul 30 '13 at 16:57
  • I edited the answer. I didn't notice your original code didn't have the `=` in the formula. – stenci Jul 30 '13 at 18:14
  • Good! If you think this (or any other) is the correct answer, then mark it as answer :) – stenci Jul 30 '13 at 18:38
0

As per Jaycal's comment try this:

dim rowNum as integer
Range("M1").Select
rowNum=activesheet.cells(rows.Count,activecell.Column).end(xlUp).row
ActiveCell.FormulaR1c1 = "=SUM(R[2]C:R[" & rowNum & "]C)"

where using the cells notation means we can refer to row then column, row is taken as the max rows in worksheet, column is the active cell - which makes this a bit more reusable than referrring to range("M" & rows.count) given you've selected the cell anyway.

RowanC
  • 1,611
  • 10
  • 16