auto add rows to ranges

is there any way when i insert a row to have it join a range? I want to
define one row as a range and then any rows i insert on top of it to be
added to that range automatically. Nay ideas? Thanks for any help.

Use a defined name as the definition of the range

Insert => Name => Define

Name: List
RefersTo: =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),10)

change 10 to reflect the number of columns.

Then in you code you can use

set rng = Range("list")

or as advised before


set rng = Range("A1").CurrentRegion

would do the same.

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> is there any way when i insert a row to have it join a range? I want to
> define one row as a range and then any rows i insert on top of it to be
> added to that range automatically. Nay ideas? Thanks for any help.
>

Hi,
This is a non VB, low tech solution and it does not not work, if the named
range includes Row 1. Include the row above what you want in the named range
and when you insert a row, it will automatically be included. Example,
desired named range is B3-B5, the relavant range, but when you establish the
range make it B2-B5. When you insert a row at B3, it will be included in the
existing named range.

"[email protected]" wrote:

> is there any way when i insert a row to have it join a range? I want to
> define one row as a range and then any rows i insert on top of it to be
> added to that range automatically. Nay ideas? Thanks for any help.
>
>