PDA

View Full Version : Continued: pivot table counting blank cells in data table



nicko
09-23-2008, 07:39 AM
Hi all,

I'm still battling with a problem.

I have a pivot table which is counting blank cells from the range of data that the pivot table is reading from.

It appears some of the cells have spaces in them so they appear blank in the data range but the pivot table is counting them.

Is their a way I can clear these cells with spaces in, so the pivot table reads them as blank?

Many thanks,

Nicko

MaximS
09-23-2008, 10:33 AM
before you creating pivot table you can use excel function:

=trim(A1)

this will remove extra spaces at the front or at the end of string

then copy/paste special as values

and bit of descending sorting will leave blanks to the bottom.

Slyboots
09-23-2008, 11:11 AM
If the cells with "real" values are all numeric, you can clear the range of spaces like this:

Selection.SpecialCells(xlCellTypeConstants, 2).ClearContents

The "2" parameter selects Text constants.

S



Hi all,

I'm still battling with a problem.

I have a pivot table which is counting blank cells from the range of data that the pivot table is reading from.

It appears some of the cells have spaces in them so they appear blank in the data range but the pivot table is counting them.

Is their a way I can clear these cells with spaces in, so the pivot table reads them as blank?

Many thanks,

Nicko

Bob Phillips
09-23-2008, 11:39 AM
Cross-posted at OzGrid http://www.ozgrid.com/forum/showthread.php?t=108709

dominicb
09-23-2008, 12:36 PM
Cross-posted at OzGrid http://www.ozgrid.com/forum/showthread.php?t=108709
Also cross posted here(twice)
http://www.excelforum.com/excel-general/656385-pivot-table-counting-blank-cells-in-data-table.html
http://www.excelforum.com/excel-general/656497-pivot-table-counting-blank-cells-in-data-table-continued.html

DominicB

nicko
09-24-2008, 06:26 AM
If the cells with "real" values are all numeric, you can clear the range of spaces like this:

Selection.SpecialCells(xlCellTypeConstants, 2).ClearContents

The "2" parameter selects Text constants.

S

the cells with real values are all text. Is there a way to do the same thing but leave the text cells and still clears the other cells?

nicko
09-24-2008, 07:07 AM
ok, is there an obvious problem with this...


Selection.SpecialCells(xlCellTypeBlanks, 4).ClearContents

what i am trying to do is clear the contents of all the blank cells