ExcelBanter - Range setting for pivot table

Can you pick out a column that always has data in it if that row is used?

If yes, you could do something in code, but I think it would be easier to create
a dynamic name that adjusted when the data changed--then you could just refresh
the pivottable instead of rebuilding it.

Debra Dalgleish explains dynamic range names he
http://contextures.com/xlNames01.html#Dynamic

Insert|Name|define
Names in Workbook: RangeT
Refers to: =OFFSET('aging_data'!$A$1,0,0,COUNTA('aging_data'! $A:$A),16)

This expects that column A always has something in it if that row is used. If
you don't have a column that can be used to determine the last row, then this
formula won't work.

If you really wanted this in code, record one while you create the name and
you'll be set.

aw wrote:

Just want to know the way to set the range property for creating pivot table
provide that the number of row will change by each time.

Eg. The following line R137, today may be 137 total number of row & may be
237 for tomorrow report.

If I just amend the range from R137 to R10000 (aim to cover all data), error
will appear when running the macro if the number of row changed by next time.

Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="rangeT",
RefersToR1C1:="=aging_data!R11C1:R137C16"

--
aw

--

Dave Peterson