If you use a named range (Insert -- Name -- Define) and under 'Refers to'
use the offset formula to define a range (eg:
=OFFSET(Data!$A$1,0,0,MAX(2,COUNTA(Data!$A:$A)),11 ) - the max statement
ensures that if your data range returns no data, the pivot table will still
work. Look under Help for details of how to use the offset function properly)

Then use the named range as your pivot table source data.

This assumes that you are using formulas at the side of the query to process
the data further. If all of your data is in the query, then you can use the
name of the query as the data range for the pivot table.

To have everything automatically update when you refresh the query, you need
to disable the background refresh in the query (under data range properties
-- Refresh control) , and in table options on the pivot table, check the box
by 'refresh on open'

I hope that helps!

Richard

"matpj" wrote:


Hi there,
I have a pivot table on one worksheet, that is refreshed from a range
of data on a second worksheet.

the data is refreshed using a query, so can contain different numbers
of rows.

how can I code it to always refresh using the complete range from the
data worksheet?

can anyone help?


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=477871