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?

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 InsertNameDefine, 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" wrote in message
...
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

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?