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

[Katrina]

I have a spreadsheet that a VB program will pull a range of cells to
insert into a Word document. VB looks for Excel's Name "TableRange1".
I need to dynamically determine what that range is.

I want to determine it based on the first "empty cell" it comes upon.
The cell will have a formula but no data (text in this case).

For example, in cell range A1:C30, if A15 is the first blank cell then
I want my range to be A1:C14. I'd like to be able to do this using
formulas but I'm willing to learn a macro if necessary.

Thanks
Katrina Haggerty

 

[Katrina]

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 here:

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]

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.