Printable View
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
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.
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
Quote:
Originally Posted by nicko
Cross-posted at OzGrid http://www.ozgrid.com/forum/showthread.php?t=108709
Also cross posted here(twice)Quote:
Originally Posted by xld
http://www.excelforum.com/excel-gene...ata-table.html
http://www.excelforum.com/excel-gene...continued.html
DominicB
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?Quote:
Originally Posted by Slyboots
ok, is there an obvious problem with this...
what i am trying to do is clear the contents of all the blank cellsCode:Selection.SpecialCells(xlCellTypeBlanks, 4).ClearContents