Dynamic range (column); include only rows before blank row above more data

L

L Mehl

Hello:

One worksheet will contain more than one table holding lookup values
(prices, CPU speed, etc.). They will be located one on top of another, with
possibly some other data between tables, separated by at least one blank
row. They will be expandable by row, as new prices, CPUs, etc. are added.

I want the range name to expand and reflect all the rows in a table.

Using a column as an example of tables,

a (in A1)
b
c

d
e
f

represent 2 tables (ranges).

Excel Hacks has the code for Define Name "Refers to" as
=OFFSET(Sheet1!$A$1,0,0,MATCH("*",Sheet1!$A$1:$A$100,-1),1)

This formula does not see the blank between c and d as a separator of two
ranges. It shows the range as a through f.

If I blank d, e, and f, it correctly defines the range as a through c.

Can someone tell me the code for "Refers to" that will recognize
a
b
c
as the first range?

If the code were correct, would the definition of the second range be
=OFFSET(Sheet1!$A5,0,0,MATCH("*",Sheet1!$A5:$A$100,-1),1)

Thanks for any help.

Stephen Mehl
[email protected]
 
L

Lynn Arlington

I have used the xldown command as follows:

Range("A1").Select
ActiveCell.End(xlDown).Select
LastRow = ActiveCell.Row
Range("a1:e" & LastRow).Select

This selects to column e only the rows used when pressing CTRL+DOWN
 
L

L Mehl

Lynn --

Thank you.

I was hoping for code to use in the range name definition. I will see about
incorporating this in the calculations.

Larry
 
I

icestationzbra

larry, correct me if i am wrong. even the formula given on th
contextures page will not ensure that the named range picks up valu
only from contiguous regions. if it does, let me know, i would like t
use it too.

mac
 
L

L Mehl

Mac --

I think you are correct. I have not built the model yet, but it looks like
I will have to have a different formula for each set of tables stacked
vertically,
where the cell name in which to start the next table's search for the last
row will be 2 greater than the number of the row containing the last record
in the table above (assuming the tables are separated by 1 blank row).

Does this help?

Larry
 
Top