Reference to a dynamic range

Hi,
Is there a simple/elegant way to refer to a range that is constantly changes?
For example if I have a sheet that holds some data in a range and it is
being constantly updated by adding rows or removing rows, is there a way to
refer to the current range without the need to change the formulas every time
a row is being added or removed?

See the advice at this link

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



"Yossi" wrote:

> Hi,
> Is there a simple/elegant way to refer to a range that is constantly changes?
> For example if I have a sheet that holds some data in a range and it is
> being constantly updated by adding rows or removing rows, is there a way to
> refer to the current range without the need to change the formulas every time
> a row is being added or removed?

Hi Yossi,

The simplest way would be to use range names - this way you only need to
re-define the range name to update the formule.

Let's say you have a list on one sheet, and a Pivot Table on the other.
Rather than having to repoint the Pivot Table at the whole new range, if the
Pivot Table was just based on range 'PivotData', all you would have to do
would be to update the definition of the 'PivotData' range.

This can be done manually - select the block, and Insert>Name>Define, or you
can use the macro below (adjust as necessary).

Sub SetRange()

Sheets("Sheet1").Select
Range("A5").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="rangename2",
RefersToR1C1:=Selection.CurrentRegion

End Sub

Hope this helps

Sunil Jayakumar

"Yossi" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> Is there a simple/elegant way to refer to a range that is constantly
> changes?
> For example if I have a sheet that holds some data in a range and it is
> being constantly updated by adding rows or removing rows, is there a way
> to
> refer to the current range without the need to change the formulas every
> time
> a row is being added or removed?

www.ayyoo.com/loans.html