How do I name a range and then have that range be dynamic?
 

I want to select the entire contents of a worksheet, but that data set
will change each time I open it as records will be added to it every day.

I want to always choose all records, and there will never be any
non-contiguous rows in the data or blank rows in it.

So I can make a direct call to it and always get all of it by way of
the named range.

How do I name a range and then have that range be dynamic?
 

If A1 is the header for example for the first column you can use

Range("A1").CurrentRegion.Select

The same as Ctrl *

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote in message
...
I want to select the entire contents of a worksheet, but that data set
will change each time I open it as records will be added to it every day.

I want to always choose all records, and there will never be any
non-contiguous rows in the data or blank rows in it.

So I can make a direct call to it and always get all of it by way of
the named range.

How do I name a range and then have that range be dynamic?
 

By selecting any cell in the range you can use
selcetion.goto.currentregion and name it using code

How do I name a range and then have that range be dynamic?
 

myName:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

If only for use in VBA, you say 'Select', simply
ws.Range("A1").CurrentRegion.Select

Both the above assumes your first record starts in A1

Regards,
Peter T


"CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote
in message ...
I want to select the entire contents of a worksheet, but that data set
will change each time I open it as records will be added to it every day.

I want to always choose all records, and there will never be any
non-contiguous rows in the data or blank rows in it.

So I can make a direct call to it and always get all of it by way of
the named range.