Pivot table with blanks

All,

I couldn't find an answer to this on a quick search so thought I would throw it out to all of you.

I have a pivot table that is generated from constantly changing data, the number of columns stays the same, but the number of rows grows and shrinks depending on the amount of info pulled in each week. At the moment what I do is each week I go back and change the pivot table data source to the correct amount of rows each week. This is a bit of a pain and I would like to be able to use a different method. I had thought about using a formula to set up a dynamic named range but the only issue is that there are blank cells in basically every column. There may be a blank in a4 and then another in c8, etc....

The pivot table is not grouped or manipulated in any way after it is generated, data is pulled out of it into another set of formulas.

So to make a long question short.

Can I just set up my pivot table to do 9999 rows and forget it or will this cause unforeseen difficulties?

Thanks in advance.

Tim

If there is at least one column with no blanks, then you can use that column to define the number of dynamic rows in your named range formula....

... setting to 9999 shouldn't be a problem, I don't think, until you surpass 9999. You will probably get a blank category in your PT, though....

Then probably the overestimation of rows is easiest way... unless you want a VBA method to determine last row containing any data... but that wouldn't be me, unfortunately, to supply that method....