Using your example, the formula to define TableRange1 would be:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B:$B),3)
This will create a range with rows equal to the number of items in
column B, and 3 columns.
Katrina wrote:
Debra Dalgleish wrote in message ...
You could use a dynamic formula to define the range in Excel. It will
automatically adjust to include only the filled rows. There are some
instructions he
http://www.contextures.com/xlNames01.html#Dynamic
That's not working for me. I'm getting a #VALUE! error.
I realized I should give a little more information. I'm using Windows
2000, Excel XP and the VB program is created with VB 6.
Here is what my table will look like:
Phone number Reason Code Volume
800-555-2222 Abandon Call 6
Misc. Text 10
More Misc. Text 101
800-555-1111 Abandon Call 305
Text Text Text 3
More Text 4
For each report VB will dump the data into my Spreadsheet which may
consist of 3 rows or 30 rows. When VB copies and pastes this table
from excel I want it to know where the table should stop. VB does
know to look for the Name "TableRange1" which is usually an absolute
value (for example, A3:C24)but in this case I need to create it
dynamically with each report.
I hope that makes things clearer. I would appreciate any help anybody
can give me.
Thanks,
Katrina Haggerty
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html