Pivot table on MS Query refreshable spreadsheet

[goofaholix]

I have a refreshable spreadsheet which populates a data tab from MS Query.

I've based a Pivot table on this refreshable data, because I don't know how many rows I will get I've selected the entire columns as my Pivot table dataset.

The Pivot table lumps all the empty rows into a row headed (blank) and a column headed (blank), is there any way I can get rid of these?

I tried hiding the column and row but then if the number of columns and rows changes the wrong column is hidden.

Thanks

Bruce

http://homepages.woosh.co.nz/bf/

 

[SkipVought]

hi,

Your QueryTable has a NAMED RANGE. Use that named range in the PT source data definition.

A proper PT ought never to include unused rows of source data. That is a potentential source of all sort of of problems.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

If you are populating data using MS Query, why not populate directly into the pivot table using the "External Data" option in the pivot table build?

You then go to teh same wizard you would use for standard MS Query but the data flows directly into teh pivot table rather than having to be physically represented on the sheet 1st

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question