copy formula down and ever change range
 

I have a spreadhseet that uses the "Data", "Subtotals" in VBA obviously to
subtotal items. I want a formula copied down the entire list, after the
subtotal.
I have this all working (probably not as easily as some would have it, but
it works), except at the moment the formula is copied down a static number of
rows.

How do I copy it down to the last cell in my range, not knowing how big the
range is to start with?

Thanks
Dean

copy formula down and ever change range
 

Without knowing what your data is like it's hard to say but something like

range("a65536").end(xlup)

Which will find the last cell in col A with data in it. You can use offset
to get exactly the right cell.

Jeff

"Dean" wrote in message
...
I have a spreadhseet that uses the "Data", "Subtotals" in VBA obviously to
subtotal items. I want a formula copied down the entire list, after the
subtotal.
I have this all working (probably not as easily as some would have it, but
it works), except at the moment the formula is copied down a static number
of
rows.

How do I copy it down to the last cell in my range, not knowing how big
the
range is to start with?

Thanks
Dean

copy formula down and ever change range
 

Jeff,

I'm using autofill, you suggestion would put my formula into the bottom
63,??? cells of my spreadsheet, won't it?
I can currently autofill H8..H2000 but it's tohe H2000 that is wrong. I
need someway of saying something like
range("H8")
selection.end(xldown)
rng = activerow.selection

and then using rng in place of my H2000

Dean

"Jeff Standen" wrote:

Without knowing what your data is like it's hard to say but something like

range("a65536").end(xlup)

Which will find the last cell in col A with data in it. You can use offset
to get exactly the right cell.

Jeff

"Dean" wrote in message
...
I have a spreadhseet that uses the "Data", "Subtotals" in VBA obviously to
subtotal items. I want a formula copied down the entire list, after the
subtotal.
I have this all working (probably not as easily as some would have it, but
it works), except at the moment the formula is copied down a static number
of
rows.

How do I copy it down to the last cell in my range, not knowing how big
the
range is to start with?

Thanks
Dean

copy formula down and ever change range
 

Sounds like you're almost there.

range("H8").end(xldown).address

would give you H2000 (or whatever the last cell is), for example. Assuming
there are no gaps. Although I may still be misunderstanding your question.

Jeff

"Dean" wrote in message
...
Jeff,

I'm using autofill, you suggestion would put my formula into the bottom
63,??? cells of my spreadsheet, won't it?
I can currently autofill H8..H2000 but it's tohe H2000 that is wrong. I
need someway of saying something like
range("H8")
selection.end(xldown)
rng = activerow.selection

and then using rng in place of my H2000

Dean

"Jeff Standen" wrote:

Without knowing what your data is like it's hard to say but something
like

range("a65536").end(xlup)

Which will find the last cell in col A with data in it. You can use
offset
to get exactly the right cell.

Jeff

"Dean" wrote in message
...
I have a spreadhseet that uses the "Data", "Subtotals" in VBA obviously
to
subtotal items. I want a formula copied down the entire list, after
the
subtotal.
I have this all working (probably not as easily as some would have it,
but
it works), except at the moment the formula is copied down a static
number
of
rows.

How do I copy it down to the last cell in my range, not knowing how big
the
range is to start with?

Thanks
Dean