Continued: pivot table counting blank cells in data table

[nicko]

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]

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]

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]

Cross-posted at OzGrid http://www.ozgrid.com/forum/showthread.php?t=108709

[dominicb]

Cross-posted at OzGrid http://www.ozgrid.com/forum/showthread.php?t=108709

Also cross posted here(twice)
http://www.excelforum.com/excel-gene...ata-table.html
http://www.excelforum.com/excel-gene...continued.html

DominicB

[nicko]

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]

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