I've tried the Dynamic Range. It's adding columns, but it does not remove
columns. I will have situations where data is brought in dependant upon a
date range. If the date range returns fewer records than the prior run, the
range is not shrinking and blank rows are included. This then makes any
attempts at trying to do grouping within the Pivot table impossible.

Can the Dynamic Range shrink in size?

"Dave Peterson" wrote:

Maybe you can use a dynamic range name

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Rob wrote:

I have a Pivot Table which is populated by a worksheet which itself is
populated via a SQL Server Stored Proc, using OLE DB. I get the RowCount of
the worksheet that has the data read into it, and use it for the Upper Row
limit of the Pivot Table Range, using the following code:

Sheets("Projected Shipments").PivotTableWizard SourceType:=xlDatabase,
SourceData:= _
"Sheet1!R1C1:R" & dRowCount & "C29"

Where dRowCount is my 'dynamic' number of rows returned by the SQL
Statement. The problem is after my processes run, the Upper range for the
Pivot table data is not the dynamic value I supply above.

How can I get it so that I can dynamically and correctly reset the
boundaries (rows) for the PivotTable cache to get it to include all of my
rows?

Thank you

--

Dave Peterson