I need to determine a cell range based on a blank cell

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

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