VBA for selecting continuous cells from relative starting point?

V

Valerie

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
 
T

Trevor Shuttleworth

Valerie

something like:

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

Regards

Trevor
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top