Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A copy of a chart doesn't change when the original has new range | Charts and Charting in Excel | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
want to copy formula, only change one number in formula | Excel Worksheet Functions | |||
change a range colour after copy to another worksheet | Excel Programming | |||
copy formula in a range | Excel Programming |