VBA for selecting continuous cells from relative starting point?
 

In a spreadsheet "cleanup" macro, I can get to the last column of numbers and
write a subtotal formula. I want to copy this formula and paste to the five
columns to the left of this reference. How can I do this once instead of for
each column? This is how I have it now to paste into the one cell to the
left:

ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-91]C:R[-1]C)"
ActiveCell.Copy
ActiveCell.Offset(0, -1).Select
ActiveSheet.Paste

Also, in the subtotal formula - R[-91] - is specific. Is there a way to
write this where excel finds the first value in the column no matter how many
rows need adding instead of me having to count how many I want it to add?

Thanks!
Valerie

VBA for selecting continuous cells from relative starting point?
 

Valerie

something like:

ActiveCell.Offset(1, -5).Resize(1, 5).FormulaR1C1 = _
"=SUBTOTAL(9,R[-91]C:R[-1]C)"

Regards

Trevor


"Valerie" wrote in message
...
In a spreadsheet "cleanup" macro, I can get to the last column of numbers
and
write a subtotal formula. I want to copy this formula and paste to the
five
columns to the left of this reference. How can I do this once instead of
for
each column? This is how I have it now to paste into the one cell to the
left:

ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-91]C:R[-1]C)"
ActiveCell.Copy
ActiveCell.Offset(0, -1).Select
ActiveSheet.Paste

Also, in the subtotal formula - R[-91] - is specific. Is there a way to
write this where excel finds the first value in the column no matter how
many
rows need adding instead of me having to count how many I want it to add?

Thanks!
Valerie