can we get the cell address from the name.

[shishi]

Hi,

I am pretty new to excel programming. I don't know if this is
possible.. I have defined a name like START_TABLE at the begining of a
table in excel and END_TABLE at the end as below.
---------------
| START_TABLE |
---------------
------------------------------------
|------- THE TABLE GOES HERE ---|
-------------------------------------
-----------------
| END_TABLE |
-----------------

This table can grow in number of rows. But I need to copy this table to
another worksheet. So I am trying to define a named cell like
START_TABLE at the start and END_TABLE at the end. Then trying to get
the address for the START_TABLE and the END_TABLE, then define a range
using those cell addresses. Is this a good approach? How can I get the
address for a named cell(is it START_TABLE.Address)?

Thanks
shi

 

[Tom Ogilvy]

Range(range("Start_Table"),range("End_Table")).Copy


If you really want the address

s1 = Range("Start_Table").Address
s2 = Range("End_Table").Address

 

[Guest]

hi,
I think you have a good idea but unfortunately excel doesn't think that way.
the entire table should be a named range. not just the start and stop point.
to select the table then is easy.

Range("NamedRAnge").select
msgbox Range("NamedRange").address

Is your table a solid block of data? does it start a Cell A1?
there are ways to resize the named range with code.

Regards
FSt1

 

[shishi]

Hi FSt1 & Tom,

Thanks for your reply. I wish to ask you how can we use named range
to specify a number of rows when the number of rows change. The whole
idea is something like this.

I have an excel document with couple of lines of text and then a
table, then couple of lines of text and then table and so on. I wish to
specify a named range for the table. But I don't know how? Then I wish
to copy all the tables to another worksheet with a space between
tables. I wish to use the same program even when the number of rows in
the table increase or decrease. Since I don't know the excel
programming I find a lot of difficulty. Thanks a lot for your
suggestions.
shi

 

[Tom Ogilvy]

insert=>Name=>Define

Name: List2 for example of a name you could use
Refersto: =Offset(Sheet1!$a$1,0,0,CountA(sheet1!$A:$A),10)

as an example. Adjust 10 to reflect the number of columns.