Excel name range - help

Re: Excel name range - quick & dirty solution

Rob,

Those with more knowledge than I have will no doubt have a better solution, but could you start your range at A3? Then, when you insert at row 4, the range name expands to A3:A21.

HTH,

Ian

Hi Rob

When you name a range, Excel will by default use an Absolute address eg; $A$4:$A$20

Sub TryThis()
Range("A4:E20").Name = "TheRange"
Range("TheRange").Rows(4).EntireRow.Insert
End Sub

Dave

OzGrid Business Applications

Rob, simply define your named range with the formula,
=INDIRECT("A4"):$A$20, in the Define Name dialog's
"Refers to:" field. As each row is inserted the
reference to cell A20 will be automatically
incremented and the range extent will always include
cell A4.

Rob, believe me, there is no need for a formula!

Name the range using using Absolute cell addresses (Excels default) and it will extend whenever you add a row or rows within the named range.

Dave

OzGrid Business Applications

A range that is named via the Name Box can only be extended by inserting a row within the named range. When you add data to the end of the named range, the added row/data will never be covered. If you use a formula to give a name to your range, you can insert rows within the range as well as at the end of the range. The latter is also called a named dynamic range. It has some performance costs associated with it if your spreadsheet is huge. There is also an alternative way of creating a dynamic range with lesser costs.

Aladin

=======================================

You can add many dynamic named ranges (see "Dynamic Ranges" on my Wesite) to a Workbook with any noticable effect to performance.

The biggest culprit effecting Workbook performance are without doubt Array formulas (See "Array Formulas on my Website).

An alternative way to create a dynamic named range is listed on my Website also under "VBA Tips and Tricks"

Dave
OzGrid Business Applications

You may be better of using....

Hi Rob

To save all the confusion, here is just one of many ways to create a named range that is bounded by A4 and Ewhatever

Range("A4", Range("E" & Range("A65536").End(xlUp).Row)).Name = "TheRange"

Dave
OzGrid Business Applications

A bit more about INDIRECT()

Rob, in your posting below you requested a way to
create a named range that would always include
row 4 and expand downwards as new rows are inserted.
As I indicated in my earlier posting using
=INDIRECT("A4"):$A$20 accomplishes this to a tee.
In fact, this use of INDIRECT() is well documented
and recommended by Microsoft in the Help topic for
"INDIRECT worksheet function". This reference
clearly states:

"When you create a formula that refers to a cell,
the reference to the cell will be updated... if
the cell is moved because rows or columns are
inserted or deleted. If you always want the formula
to refer to the same cell regardless of whether
the row above the cell is deleted or the cell is
moved, use the INDIRECT worksheet function. For
example, if you always want to refer to cell A10,
use the following syntax: INDIRECT("A10")"

I also want to impress upon you that you are not
obliged to use INDIRECT() as part of an named range.
You can also use this approach directly in your
formulas. For example, suppose that your wanted
to sum the contents of this ever increasing cell
range. You could also use =SUM(INDIRECT("A4"):$A$20)
and not even bother with a named range.

Hope this clarification helps.