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

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$1 00,-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

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 Mehl" wrote:

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$1 00,-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

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


"Lynn Arlington" wrote in message
...
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 Mehl" wrote:

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$1 00,-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

Check out this website

http://www.contextures.com/xlNames01.html

"L Mehl" wrote:

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


"Lynn Arlington" wrote in message
...
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 Mehl" wrote:

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$1 00,-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

Thanks Lynn.

I found some other useful techniques there, as well.

Larry


"Lynn Arlington" wrote in message
...
Check out this website

http://www.contextures.com/xlNames01.html

"L Mehl" wrote:

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


"Lynn Arlington" wrote in
message
...
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 Mehl" wrote:

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$1 00,-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

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

mac.


---
Message posted from http://www.ExcelForum.com/

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


"icestationzbra " wrote in
message ...
larry, correct me if i am wrong. even the formula given on the
contextures page will not ensure that the named range picks up value
only from contiguous regions. if it does, let me know, i would like to
use it too.

mac.


---
Message posted from http://www.ExcelForum.com/