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