Pivot table Blanks

[Dkso]

When using a pivot table I always have a row at the bottom called
Blank, even when I cant see any blank rows in the data, how can I get
rid of this.


Thanks for any help provided

Dean

(e-mail address removed)
www.dkso.co.uk/
http://homepage.ntlworld.com/dkso

 

[Earl Kiosterud]

Dean,

"Blank" can happen when the data range for the pivot table extends beyond
the last record in your data. If you have a varying count of rows, and at
least one column has no empty cells (to the last record, not beyond), you
can use the dynamic range technique. Post back.

Another option is to right-click the "blank" pivot table field, and choose
hide.

 

[Dkso]

Earl,
thanks for your reply and sorry the the delay in mine. If I read your
message correctly it's because I use a static range for my data, this
range is the full columns A -> whatever, the data in this range is
updated by an MSQuery which will have varying amounts of data each
time.

Without having to select the data each time is there another way to
set the range?

Dean

 

[Debra Dalgleish]

When you get data with MS Query, it should be in a range that's
automatically named, e.g. Query_from_MS_Access_Database
To see the name, select the sheet that contains the data, and choose
Insert>Name>Define

Change the pivot table to use this range as the source:
Select a cell in the pivot table
Choose PivotTable>Wizard
Click the Back button
Enter the MS Query range name
Click Finish

 

[Dkso]

Debra,
That works fine thanks, however as well as the MSQuery I also have
extra formula that are copied down when the query is re-freshed, these
don't appear to be in the Query Range, obviously as I have created the
formula I want to have this info in the Pivot table !

Any suggestions please.

Thanks
Dean

(e-mail address removed)
www.dkso.co.uk/
http://homepage.ntlworld.com/dkso

 

[Debra Dalgleish]

You can name a range that is offset from the query range:

Choose Insert>Name>Define
Type a name for the range, e.g. PivotData
Type an offset formula, e.g.:
=OFFSET(Sheet1!Query_from_MS_Access_Database,0,0,,10)
where 10 is the number of columns required to include your calculations
Click Add, click OK

Use this range as the source for the pivot table.